Set Analysis Not working

[MoCho]

Hi

I'm trying to do period comparisons in Qlik Sense - Current month vs Last Month vs Same Month Last year etc. I have the following variables in my load script:

let vMaxDate = '=max(Date)';
let vMaxDay = '=day(max(Date))';
let vMaxMonth = '=month(max(Date))';
let vMaxYear = '=max(Year)';
let vPriorMonth = '=month(addmonths(max(Date),-1))';
let vPriorMonthYear = '=Year(addmonths(max(Date),-1))';
let vPriorYear = '=vMaxYear-1';

On my sheet, I created a Master measure as follows:

SUM({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'},Day=,Date=,MonthYear=,MonthNum=>}NetSalesValue)

The issue I'm facing is that the result is the sum of the Max Year. The Month part is not being considered even though is shows as OK and is being picked up in the expression window. See attached image.

Am I missing something?

Also, how would I the go about calculating the other periods?

[qv_testing]

Variables will not work as you created and my suggestion is to create those variables in UI.

[Rohan · Accepted solution]

Hi Mocho,

In your image, you can see that Month & Day are not getting considered as Dimensions, instead Qlik is considering them as Qlik Functions. Please check if the dimension names are correct for them or not in your script. if the names are correct then try placing them in '[..]' once & check if that is working.

PS : Fields will be in Orche/Yellowish colour & Functions in Blue in expressions.

Regards,

Rohan. 

[Chanty4u]

Try this

SUM({<Year={'$(vPriorMonthYear)'}, Month={'$(vPriorMonth)'}>} NetSalesValue)

Same month last year

SUM({<Year={'$(vPriorYear)'}, Month={'$(vMaxMonth)'}>} NetSalesValue)

[vinieme12]

 is Month field Text or Numeric?

Alternatively  You can filter for current month as as below 

=SUM({< Date ={">=$(=Date(MonthStart(Max(Date))))<=$(=Date(Max(Date)))"} >}NetSalesValue)

[MoCho]

Thanks Rohan. You were right, my Month field had a different name.

[MoCho]

Thank you

These worked for the other periods