Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have as dimension of my data-set the MonthYear
I would like to select 3 Months Nov-2017, Dec-2017, Jan-2018 (expression A) and then based on this selection have the possibility to compare with the same month of the prior year-> Nov-2016, Dec-2016, Jan-2017 (expression B).
Expression A is really simple Sum(Amount)
How i can build the expression B?
Thank you!
Since you are using MonthYear field, you have to use formatting to match it's formatting. Try like:
=if(vExcludeInstrumentSales,sum({<MonthYear={">=$(=Date(MonthStart(Min(Invoice_Date_Num),-12),'MMM-YYYY')) <=$(=Date(MonthEnd(Max(Invoice_Date_Num),-12),'MMM-YYYY'))"},Area-={'Area-not-available'},Country_Desc={'Italy'}>}ExtendedOriginal),Sum({<MonthYear={">=$(=date(MonthStart(Min(Invoice_Date_Num),-12),'MMM-YYYY')) <=$(=Date(MonthEnd(Max(Invoice_Date_Num),-12),'MMM-YYYY'))"},Area-={'Area-not-available'},Item_Category={$(vNoInstruments)},Country_Desc={'Italy'}>}ExtendedOriginal))/1000
Do you have a dimension with the Year?
Then you can do something like that : sum({<Year={$(=Year-1)}>}Amount)
I tried but doenst work since some months belongs to different year (maybe)
The month-year is linked also to the year, but i assume i have to reduce 12 month based on the month-year selected.
Try like:
sum({<Date={">=$(=MonthStart(Min(Date),-12)) <=$(=MonthEnd(Max(Date),-12))"}>}Amount)
i tried with this one but is not working
sum({<Year={">=$(=MonthStart(Min(Date((MonthYear),'MMM-YYYY'),-12)) <=$(=MonthEnd(Max(Date((MonthYear),'MMM-YYYY'),-12))"} Amount)
any idea?
I made a correction above. You have to use Date field in the set analysis comparison
Hi Tresesco,
probably i'm missing something - i attecched the qvw.
selecting dec-17 and jan-18 i expect to have: 197 as "Sales Selected" and 218 as "sales minus 12 months"
Can you kindly check what i'm doing wrong?
Thank you
Since you are using MonthYear field, you have to use formatting to match it's formatting. Try like:
=if(vExcludeInstrumentSales,sum({<MonthYear={">=$(=Date(MonthStart(Min(Invoice_Date_Num),-12),'MMM-YYYY')) <=$(=Date(MonthEnd(Max(Invoice_Date_Num),-12),'MMM-YYYY'))"},Area-={'Area-not-available'},Country_Desc={'Italy'}>}ExtendedOriginal),Sum({<MonthYear={">=$(=date(MonthStart(Min(Invoice_Date_Num),-12),'MMM-YYYY')) <=$(=Date(MonthEnd(Max(Invoice_Date_Num),-12),'MMM-YYYY'))"},Area-={'Area-not-available'},Item_Category={$(vNoInstruments)},Country_Desc={'Italy'}>}ExtendedOriginal))/1000
I guess you marked your reply as correct by mistake. Mark the right one so that people looking for similar solution don't get confused and I get rewarded.