Hi All,
We have period based data where period consists of rolling 6 week, rolling 6 month, rolling 3 Quarters . We have calculated columns for period values say, (profit/sales).
We need to calculate WoW values for all the calculated columns.
for example
First create a calc field for finding the start date of Rolling 6 week. Lets call it ROLL6WK_START_DT
Create another calc field ROLL_6WK_PROFIT
ifelse(Date_col >= ROLL6WK_START_DT, Profit, 0)
Sum(ROLL_6WK_PROFIT) will give the Rolling 6 Weeks Profit value
Like this need to create calc fields for other time periods
Thanks for your response Vignesh.
But We dont have any dates in the data. we are getting all the measures with respect to period where period have rolling 6 Week like w7,w8,w9,w10,w11,w12.
In the table, we have 3 columns Period, Profit, Sales. This table is getting updated automatically every month with rolling 6 weeks data.
Now we have to calculate WoW too. which will be difference of last 2 weeks.
@anshulsri This is all going to greatly depend on the shape of your raw data. I am assuming your raw data looks like the transpose of what you have in your first screenshot (3 columns: Period, Profit, Commission, with 6 rows: one for each period/week). First you will need a calc to split off the week number, so it is an actual number and not a string:
parseInt(right(period, strlen(period)-1))
Then Latest Week would be:
ifelse(max(Week#, [ ])=Week#, profit, 0)
Previous Week would be:
Then Latest Week would be:
ifelse(max(Week#, [ ])-1=Week#, profit, 0)
And WoW would be:
sum(Latest Week) = sum(Previous Week)
You are not going to be able to achieve that exact layout where you have all periods listed as columns, with one more for WoW. You will probably need to do the WoW in a separate visual and then place it to the right and perhaps use the Free Form Layout mode (in Settings in the analysis) to cover up the headers of your second visual by placing it behind the first one.
Hello Jesse,
I have a doubt with free form layout which will use POP calculations. I have created a dashboard, where I have calculated WOW, MOM, QOQ, YOY for various metrics. I’m using parameters to have the dynamic view of these WOW, MOM, QOQ, YOY as below.
ifelse(${viewtype}=‘Week’, periodOverPeriodPercentDifference(sum(ops),Date, WEEK,1),
ifelse(${viewtype}=‘Month’, periodOverPeriodPercentDifference(sum(ops),Date, MONTH,1),
ifelse(${viewtype}=‘Quarter’, periodOverPeriodPercentDifference(sum(ops),Date, QUARTER,1),
ifelse(${viewtype}=‘Year’, periodOverPeriodPercentDifference(sum(ops),Date, YEAR,1),0))))
I’m also using the date hider to display the POP without nulls. And that date hider is given as filter to the sheet. so that the metrics are displayed based on the date we select.
ifelse(${viewtype}=‘Week’, minOver(min(truncDate(‘WK’, Date)),[truncDate(‘WK’, Date)])
, ${viewtype}=‘Month’, minOver(min(truncDate(‘MM’, Date)),[truncDate(‘MM’, Date)]),
${viewtype}=‘Quarter’, minOver(min(truncDate(‘Q’, Date)),[truncDate(‘Q’, Date)]),
${viewtype}=‘Year’, minOver(min(truncDate(‘YYYY’, Date)),[truncDate(‘YYYY’, Date)]),minOver(min(Date),[Date]))
Now, in this case, I will have metrics displayed for last 4 weeks/moths/quarters/years and I would need POP for only the last period in the selected date range . Any help on this?
In addition to the above question, I would like to know if there is a possibility of using the week number, month number, quarter number to the filters, without changing the dynamic feature that is applied. Also, I would like to check if the row name can be dynamically changed based on the view_type to week number /month number/quarter number instead of dates in the row names. currently it is displaying week/month/quarter/year start date, as I have used date trunc function for dates
Hi @narmada that’s a lot to process 
Why are there 4 weeks/months/etc shown? Becuase they picked a wide range of dates in the date hider control? Maybe you can create a denseRank() calculation to rank the date values in DESC and just filter that =1. Something like denseRank([max(your date field) DESC).
For the other questions it’s hard for me to picture what you are looking for - can you share a screenshot or mock?
Hello Jesse,
here is the detailed explanation and my questions.
I have few metrics, for which I need to create WOW, MOM, QOQ, YOY. I have created it in a single table, such a way that, changing the period in view_type parameter, will change the table to have corresponding values of that period.
Select_ date = ifelse(${viewtype}=‘Week’, minOver(min(truncDate(‘WK’, Date)),[truncDate(‘WK’, Date)])
, ${viewtype}=‘Month’, minOver(min(truncDate(‘MM’, Date)),[truncDate(‘MM’, Date)]),
${viewtype}=‘Quarter’, minOver(min(truncDate(‘Q’, Date)),[truncDate(‘Q’, Date)]),
${viewtype}=‘Year’, minOver(min(truncDate(‘YYYY’, Date)),[truncDate(‘YYYY’, Date)]),minOver(min(Date),[Date]))
here, date is in the format - Aug 13, 2023
ifelse(${viewtype}=‘Week’, periodOverPeriodDifference(ASP,Date, WEEK,1),
ifelse(${viewtype}=‘Month’, periodOverPeriodDifference(ASP,Date, MONTH,1),
ifelse(${viewtype}=‘Quarter’, periodOverPeriodDifference(ASP,Date, QUARTER,1),
ifelse(${viewtype}=‘Year’, periodOverPeriodDifference(ASP,Date, YEAR,1),0))))
when I create table, I should use date. I mean the table now shows the date format as Aug 13, 2023.
Q1. But the end user wants to have week number, when the view type is selected as week, likewise for other periods(month, quarter, year)
Q2. I have Select_ date(date hider) as the filter for the user to select time period. currently, I’m using relative dates. but the user want week number, month number, quarter number, instead of the date. At the same time, I would not want the other logics to be disturbed.
Q3. I’m trying to get wow as seperate column using free form lay out. but I’m able to get the vaues for all the selected period. for example, when I select last 4 weeks in my filter, wow shows for all 4 weeks. but I need wow for the last week. likewise for other periods as well.
Thanks
