In Qlik Sense - Having challenge to get a max selected year/month working in pivot table

[vvira1316]

Hi,

I'm having a challenge to get max selected year-month value to show correct results in pivot table. Also I'm unable to get previous month values to work in pivot table.

Variable Definitions

vGetSelectedYear

Max([Reporting Cal Year])

vGetSelectedMonth

Max([Reporting Cal Month])

vGetSelectedYearMonthFromYearNMonth

Date#($(vGetSelectedYear) & '-' & $(vGetSelectedMonth), 'YYYY-M')

vGetPreviousYearMonthFromYearMonth

date(AddMonths($(vGetSelectedYearMonthFromYearNMonth),-1), 'YYYY-MMM')

vDoRFromSelectedMonthYear

Date#($(vGetSelectedMonth) & '/1/' & $(vGetSelectedYear), 'M/D/YYYY')

If I've multiple months selected then Pivot is showing numbers added up for all selected months instead of just the max month from selection.

as seen in below screen shot

I'm looking to get following results in Pivot Table Which are Month of 2018-Aug and 2018-Jul next to each other.

Hi Sunny/Robert,

Please take a look at this one and advise if any ideas you may have.

stalwar1‌, rwunderlich‌

Thanks,

Vijay

[sunny_talwar]

See what it gives when you remove the expression label

All you need as a dollar sign expansion to evaluate your expression here

Count(

{<

[CFS]={"Action Needed", "Awaiting Approver 1", "Awaiting Approver 3", "Past Due", "ITRS Review", "Approved", "ITRS Escalation"}

,[ResRisk]={"High", "Critical"}

,[Risk Acceptance Data?]={"No"}

,[Date of Reporting]={"$(=$(vDoRFromSelectedMonthYear))"}

>}

[Total of finding counts])

And then you get this

[vvira1316]

Hi Sunny,

Thanks for the reply. It does solve for the selected month. I modified the previous month same way and it does provide me the information.

But there is a strange behavior happening with month selection.

Both Pivot tables provides me information correctly if I select more than a single month value. If I select only a single month value then previous month pivot table is not showing any values. Please refer to screen shots/attached app.

[sunny_talwar]

Ignore selection in Month and Date year fields and let your date field drive selections for the previous month

[vvira1316]

Hi Sunny,

I'm not sure if I'm understanding your suggestion properly here. I'll explain what I've tried.

Business user wants to keep the Year and Month Selection separate for a smaller list of selection. If I use YearMonth field than it is a longer list and I was able to get it working using variables, but I'm trying to get it working using separate selection fields.

Based on your suggestion I've tried following. Not sure if that is what you were advising here.

$(=AddMonths(Monthstart(Max([Date of Reporting])), -1))

I'm getting 2/1/2018 for date as can be seen in label. Again if a single Month is selected then I'm not getting values to be displayed but it works when more than one month is selected.

Thanks,

Vijay

[sunny_talwar · Accepted solution]

LOL, all I was saying was to use this

Count(

{<

[CFS]={"Action Needed", "Awaiting Approver 1", "Awaiting Approver 3", "Past Due", "ITRS Review", "Approved", "ITRS Escalation"}

,[ResRisk]={"High", "Critical"}

,[Risk Acceptance Data?]={"No"}

,[Date of Reporting]={"$(=AddMonths(Monthstart(Max([Date of Reporting])), -1))"}

,[Reporting Cal Year]

,[Reporting Cal Month]>}

[Total of finding counts])

[vvira1316]

Hi Sunny,

It was funny that I didn't understand your comments for a such a simple solution. I really did a lol.. 🙂

BR,

Vijay