Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a Master Calendar with below columns
Calendar Date
Year
Month
Period (Year & Month) as Year_Month
And 1 fact table which has
Product Start Date
Product_Start_Year_Month as Year_Month
Product_ID_Start
Another fact table which has
Product End Date.
Product_End_Year_Month as Year_Month
Product_ID_End
I need to calculate
[count (Product) as per Product Start Date (Calendar current month)- count (Product) as per Product End Date (Calendar Previous Month) ] for 13 rolling months of Master Calendar. Can anybody help?
create a flag in your master calendar then use set analysis in your count
ADD THIS TO YOUR CALENDAR SCRIPT:
IF(CalendarDate >= AddMonths(MonthStart(Today()),-12) AND CalendarDate <= Today(),1,0) AS [Rolling 13MO Flag]
CREATE SET ANALYSIS MEASURE
COUNT({$<[Rolling 13MO Flag]={'1'}>}product)
you also need to have 2 master calendars (one for each fact table) or do a link table to accommodate a single calendar
Thanks for your reply Dan. I will elaborate on this issue.
I am taking 5 years of data and there will be Year, Year_Month, Quarter and Month filters for user selection. My expression for rolling 13 months is
count({ Product_Start_Year_Month = { ">=date(addmonths(Date(date#(max(Product_Start_Year_Month),'YYYYMM'),'YYYYMM')12),'YYYYMM')) <= $(=date(addmonths(date#(max(Product_Start_Year_Month},'YYYYMM'),'YYYYMM')12),'YYYYMM'))" } Product_ID_Start )
which gives me correct rolling data in line chart if user selection filter is "Product_Start_Year_Month" (belong to same table). But, when I use Year or Year_Month (from master calendar) it doesn't role (provided there is association)
Same, is the case with Product_End_Year_Month.
?
I tried your suggestion of link table but still it didn't work. Can you suggest on this?