Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

Tags (2)
1 Solution

Accepted Solutions

Re: Set Analysis - Month Comparison

May be something like this:

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

4 Replies

Re: Set Analysis - Month Comparison

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

Re: Set Analysis - Month Comparison

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.

Re: Set Analysis - Month Comparison

May be something like this:

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

Not applicable

Re: Set Analysis - Month Comparison

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

Community Browser