topic Re: Include dimension values from selected month and previous month in QlikView

Include dimension values from selected month and previous month

zakpullen — Sat, 16 Nov 2024 20:37:19 GMT

Hi,

The dimension 'Position' is made up of unique id numbers, each having a value between 0 and 1. This value can change by month. I want to compare values for each position for the selected month and the previous month. This is straightforward, except that position numbers that existed last month, but not in the selected month are not included.

How can I include positions that existed last month, but not this month?

Many thanks.

Re: Include dimension values from selected month and previous month

sunny_talwar — Thu, 20 Jun 2019 13:59:13 GMT

What expression/s are you using right now?

Re: Include dimension values from selected month and previous month

zakpullen — Thu, 20 Jun 2019 14:07:42 GMT

Selected month:

sum(aggr(Max([Position FTE]),Position))

Previous month:

sum({<MonthYear={">=$(=Date(MonthStart(Max(MonthYear),-1),'MMM-YY'))<=$(=Date(MonthStart(Max(MonthYear),-1),'MMM-YY'))"}>}aggr(Max([Position FTE]),Position))

Thanks

Re: Include dimension values from selected month and previous month

sunny_talwar — Thu, 20 Jun 2019 14:10:15 GMT

May be try this for previous month

Sum({<MonthYear = {">=$(=Date(MonthStart(Max(MonthYear),-1),'MMM-YY'))<=$(=Date(MonthStart(Max(MonthYear),-1),'MMM-YY'))"}>} Aggr(Max({<MonthYear = {">=$(=Date(MonthStart(Max(MonthYear),-1),'MMM-YY'))<=$(=Date(MonthStart(Max(MonthYear),-1),'MMM-YY'))"}>}[Position FTE]), Position))

Re: Include dimension values from selected month and previous month

zakpullen — Thu, 20 Jun 2019 14:20:30 GMT

Amazing! Thank you so much.