Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Alex
Contributor II
Contributor II

null subtotal in pivot table with pick match

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.subtotal null.PNG

 

subtotal ok.PNG

 

 

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!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

10 Replies
sunny_talwar

Would you be able to attach a sample to check this out?
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

Alex
Contributor II
Contributor II
Author

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.

Alex
Contributor II
Contributor II
Author

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,
sunny_talwar

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?

Alex
Contributor II
Contributor II
Author

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.
sunny_talwar

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), ','))
Alex
Contributor II
Contributor II
Author

1) The new expression is always 0.

2)1.PNG

 

2.PNG

 

3.PNG

 

sunny_talwar

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