topic Re: Pivot Table With Pick Match - Data Issue in QlikView

Pivot Table With Pick Match - Data Issue

manish_kumar — Tue, 21 Nov 2017 12:18:49 GMT

I want to show the Actual and planned values in pivot table for YTD and Rolling Six months.

So I am using Inline table for Actual and Planned labels and consecutively using pick match with Expressions.

But while using pick match, when I select the month then YTD values in the rows are not coming correct. Values for only current month is shown whereas the total is showing correct values i.e. of YTD.

If I don't use Pick match the values are coming correct but I'll not be able to frame the attached output.

I want to show the data in the below attached image.

Attaching the sample excel sheet for data and App for more clarification. Excel sheet contains desired output also. !

Re: Pivot Table With Pick Match - Data Issue

sunny_talwar — Tue, 21 Nov 2017 13:00:59 GMT

You need to use set analysis around your condition to avoid selections, try this

=Pick(Match(Only({1}[Matrix No]),'1','2'),
(sum({<[Date] = {">=$(=vCYStart) <=$(=vMaxDate)"}>}[Actual Visits])),
(sum({<[Date] = {">=$(=vCYStart) <=$(=vMaxDate)"}>}[Planned Visits])))

Re: Pivot Table With Pick Match - Data Issue

manish_kumar — Tue, 21 Nov 2017 16:50:24 GMT

Thanks Its working