8 Replies Latest reply: Feb 12, 2018 6:19 AM by Tresesco B

# MonthYear

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!

• ###### Re: MonthYear

Do you have a dimension with the Year?

Then you can do something like that : sum({<Year={\$(=Year-1)}>}Amount)

• ###### Re: MonthYear

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.

• ###### Re: MonthYear

Try like:

sum({<Date={">=\$(=MonthStart(Min(Date),-12)) <=\$(=MonthEnd(Max(Date),-12))"}>}Amount)

• ###### Re: MonthYear

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?

• ###### Re: MonthYear

I made a correction above. You have to use Date field in the set analysis comparison

• ###### Re: MonthYear

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

• ###### Re: MonthYear

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

• ###### Re: MonthYear

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.