topic Re: MonthYear in QlikView

MonthYear

qlikviewaf — Mon, 12 Feb 2018 09:54:27 GMT

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

jaumecf23 — Mon, 12 Feb 2018 09:59:23 GMT

Do you have a dimension with the Year?

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

Re: MonthYear

qlikviewaf — Mon, 12 Feb 2018 10:03:09 GMT

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

tresB — Mon, 12 Feb 2018 10:08:37 GMT

Try like:

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

Re: MonthYear

qlikviewaf — Mon, 12 Feb 2018 10:27:35 GMT

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

tresB — Mon, 12 Feb 2018 10:31:28 GMT

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

Re: MonthYear

qlikviewaf — Mon, 12 Feb 2018 10:45:01 GMT

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

tresB — Mon, 12 Feb 2018 10:58:57 GMT

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

tresB — Mon, 12 Feb 2018 11:19:43 GMT

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.