Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

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

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

Pivot2.PNG

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

Pivot1.png

Hi Sunny/Robert,

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

stalwar1‌, rwunderlich

Thanks,

Vijay

1 Solution

Accepted Solutions
Highlighted

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])

View solution in original post

6 Replies
Highlighted

See what it gives when you remove the expression label

Capture.PNG

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

Capture.PNG

Highlighted
Specialist II
Specialist II

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.

Pivot3.PNG

Pivot4.PNG

Highlighted

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

Highlighted
Specialist II
Specialist II

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.

Pivot5.PNG

Thanks,

Vijay

Highlighted

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])

View solution in original post

Highlighted
Specialist II
Specialist II

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