Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewaf
Creator
Creator

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
tresesco
MVP
MVP

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

View solution in original post

8 Replies
jaumecf23
Creator III
Creator III

Do you have a dimension with the Year?

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

qlikviewaf
Creator
Creator
Author

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.

tresesco
MVP
MVP

Try like:

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

qlikviewaf
Creator
Creator
Author

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?

tresesco
MVP
MVP

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

qlikviewaf
Creator
Creator
Author

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

tresesco
MVP
MVP

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

tresesco
MVP
MVP

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.