null subtotal in pivot table with pick match

[Alex]

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!

[sunny_talwar]

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

[marcus_malinow]

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]

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]

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]

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]

1) The new expression is always 0.

2)

[sunny_talwar · Accepted solution]

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