topic Re: null subtotal in pivot table with pick match in QlikView

null subtotal in pivot table with pick match

Alex — Fri, 16 Nov 2018 15:12:07 GMT

Hi all,

Do you know how can I calculate the subtotal value within a pivot table using pick match in the expression?

The subtotal is OK when I select one possible value defined in the match function, but if I do not select any or more than one I get a null.

Set analysis:
pick(Match(Fiscal_Year_Quarter,$(=concat(distinct chr(39) & Fiscal_Year_Quarter & chr(39), ','))
),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',1),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',1),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',2),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',2),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',3),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',3),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',4),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',4),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',5),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',5),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty))

Thanks!

Re: null subtotal in pivot table with pick match

sunny_talwar — Fri, 16 Nov 2018 15:45:11 GMT

Would you be able to attach a sample to check this out?

Re: null subtotal in pivot table with pick match

marcus_malinow — Fri, 16 Nov 2018 15:52:00 GMT

Hi,

I'd question why you're trying to do this using pick match

is it that your Fiscal_Quarter_Year is not associated with your InstallationDate field? If so, I would resolve this issue in the load script and avoid having to use a clunky expression. If you do so, you can simply use the expression sum(SerialNbrItemQty)

Marcus

Re: null subtotal in pivot table with pick match

Alex — Fri, 16 Nov 2018 15:54:14 GMT

Hi stalwar1,

I can't upload a sample due to it's confidential data.
I'll write down an example so maybe you can understand better what's my problem.

Example:
pick(match(status, 'A', 'B'), sum({<Product='A'>}Revenue), sum({<Product='B'>}Revenue))

Imagine we have a pivot table to display the total revenue by product. How can I get the subtotal (sum of revenue for both products) if I don't select either A or B?

Thanks.

Re: null subtotal in pivot table with pick match

Alex — Fri, 16 Nov 2018 15:57:44 GMT

Hi Marcus,

It's a little bit tricky.

I have a "reporting period" which is linked with the master calendar so with Fiscal_Quarter_Year.
Every "reporting period" has a "InstallationDate" which can be different.

Regards,

Re: null subtotal in pivot table with pick match

sunny_talwar — Fri, 16 Nov 2018 16:03:09 GMT

How do you define vQuarterStartDates? It seems to be an issue which might be related to your set analysis.

Also, the pick match statement is only used for the totals? Row level have another expression?

Re: null subtotal in pivot table with pick match

Alex — Fri, 16 Nov 2018 16:06:33 GMT

vQuarterStartDates and vQuarterEndDates are defined in the script as per below:

vQuarterStartDates = '2017-11-01','2018-02-01','2018-05-01','2018-08-01','2018-11-01'
vQuarterEndDates = '2018-01-01','2018-04-01','2018-07-01','2018-10-01','2019-01-01'

No, the pick match statement is used for the whole column.

Re: null subtotal in pivot table with pick match

sunny_talwar — Fri, 16 Nov 2018 16:11:32 GMT

Can you try few things and post an image of them

1) Can you just used this as an expression and see what you get as an output when you select 1 vs multiple Fiscal_Year_Quarter

Sum({<InstallationDate={"$(='>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',1),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',1),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date = {$(=vQuarterEndDates)}>}SerialNbrItemQty)

2) Can you also post an image of this as a separate expression in your chart. Just curious to see what it evaluates to in the chart.

=$(=Concat(DISTINCT Chr(39) & Fiscal_Year_Quarter & Chr(39), ','))

Re: null subtotal in pivot table with pick match

Alex — Fri, 16 Nov 2018 16:21:00 GMT

1) The new expression is always 0.

Re: null subtotal in pivot table with pick match

sunny_talwar — Fri, 16 Nov 2018 16:24:32 GMT

I see the problem... what all dimensions do you have in your chart? You need to add this

Sum(Aggr(
pick(Match(Fiscal_Year_Quarter,$(=concat(distinct chr(39) & Fiscal_Year_Quarter & chr(39), ','))
),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',1),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',1),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',2),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',2),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',3),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',3),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',4),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',4),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty),
sum({< InstallationDate={"$(= '>=' & Date(PurgeChar(SubField(vQuarterStartDates,',',5),chr(39)),'DD/MM/YYYY 00:00:00') & '<' & Date(Addmonths(PurgeChar(SubField(vQuarterEndDates,',',5),chr(39)),1),'DD/MM/YYYY 00:00:00'))"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty))
, YourDimensionsHere))

Re: null subtotal in pivot table with pick match

Alex — Fri, 16 Nov 2018 16:30:14 GMT

Excellent! Now it's working!

Thanks a lot :)!