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: 
Not applicable

Set Analysis - Month Comparison

I have a line chart that I'm comparing previous years with the current year.  I only want to show the past months for the current year.  How do I do this?

I have variables for the current date:

LET vToday = (today(1));

LET vCurrentMonth = Month(today(1));

LET vCurrentYear = Year(today(1));

In loading data I have:

Month([SH_ShipDate]) as MonthTxt,

Date(YearStart([SH_ShipDate]), 'YYYY') as Year;

Set Analysis:

Sum({1<[Year]={$(=vCurrentYear)}, [MonthTxt]={"<$(=Num(vCurrentMonth))"}>}[CALC_Sales])

This is showing all months in the current year with zero for those months in the future.

Thanks

1 Solution

Accepted Solutions
sunny_talwar

May be something like this:

If(MonthNum <= Num(Month(Today())), Sum({1<[Year]={$(=vCurrentYear)}, [MonthTxt]={"<$(=vCurrentMonth)"}>}[CALC_Sales]))

View solution in original post

4 Replies
sunny_talwar

Try this:

LET vToday = (Today(1));

LET vCurrentMonth = Num(Month(Today(1)));

LET vCurrentYear = Year(Today(1));

Month([SH_ShipDate]) as MonthTxt,

Num(Month([SH_ShipDate])) as MonthNum,

Year([SH_ShipDate]) as Year;

Sum({1<[Year]={$(=vCurrentYear)}, [MonthTxt]={"<$(=vCurrentMonth)"}>}[CALC_Sales])

Not applicable
Author

Thanks for the reply.  I realized after I posted that the set analysis was working, but it was acting differently than I expected.  I also tested your suggestions, and they work the same as what I had.

So the current year shows zero for all the months after Feb. of this year.  What I was hoping is that I could just not have anything show for months 3-12 of this year.  Is there a way to do this?  I want the results of the prior years for each month, but only up to the last whole month of the current year.

sunny_talwar

May be something like this:

If(MonthNum <= Num(Month(Today())), Sum({1<[Year]={$(=vCurrentYear)}, [MonthTxt]={"<$(=vCurrentMonth)"}>}[CALC_Sales]))

Not applicable
Author

Thanks Sunny.  That works exactly like I want it to.