Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

qlikviewaf
New Contributor III

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!

1 Solution

Accepted Solutions
MVP
MVP

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

8 Replies
jaumecf23
Contributor III

Re: MonthYear

Do you have a dimension with the Year?

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

qlikviewaf
New Contributor III

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.

MVP
MVP

Re: MonthYear

Try like:

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

qlikviewaf
New Contributor III

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?

MVP
MVP

Re: MonthYear

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

qlikviewaf
New Contributor III

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

MVP
MVP

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

MVP
MVP

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.

Community Browser