Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I am trying to use the below statement in Calculated Dimension, just to show the dimension (within last 12 months). and have a sum function of sale in expression.
but im getting an error or displaying 0 dimension. could you please advise me about this?
=Aggr(if(([Year Month]<=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM') And [Year Month]<=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM')) , [Month Year]), [Month Year], Company)
Kind regards,
Farrukh
Hi, Farrukh Shaikh.
Try of expression below:
Aggr(if(([Year Month]>=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM') And [Year Month]<=Date(Max(Date),'YYYYMM') ) , [Month Year]), [Month Year], Company)
P.S: be sure to check the format of the [Year Month] field if it is YYYYMM.
Hope this helps!
Hi Jason,
I tried the above as suggested but I only want to display last 12 months sales record by dimension,
My current selection is year 2016 and month is Aug.
below is the table format.
[Month Year] , Region, Company , Sales
Could you please advise me how to show all the records by range dimension.
Kind regards,
Farrukh
Hi, Farrukh Shikh.
Please, share your file qvw.
Jonas Melo.
Can you try like this ?
Dimensions
[Month Year]
Region
Company
Expressions
sum({<[Month Year]= {">=$(=date(MonthName(AddMonths(max([Month Year]),-11 ),'MMMYYYY'))) <=$(=date(MonthName(AddMonths(max([Month Year]),0 ),'MMMYYYY')))"}>})