Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
vaibhavb
Contributor
Contributor

Sum of Max Date in Pivot Table(Fiscal-Year Drill Down)

I have Pivot Table, Where used Fiscal Year (Financial Year) Drill Down as dimension and Sum of the Amount of the Period as Measure. Up to these its working fine, Now I want to add one more measure is Sum of the Max date of the Period but due to some technical difficulties i am unable to get it.

For more Information please attached Image :- 

  1.  2017-2018, 2018-2019
  2. ALL SUM :- Sum(DAYSCLOSINGBALANCE)
  3. MAX Date :- max(PRODUCTDATE)
  4. SUM of MAX Date : - SUM({$<BALANCEDATE=$(=MAX(BALANCEDATE))>} DAYSCLOSINGBALANCE)
  5. Hard Coded Max date Sum : - Sum({$<PRODUCTDATE={'09/09/2018'}>} DAYSCLOSINGBALANCE)

Here "Hard Coded Max date Sum" column is just for the Test purpose, Which is exactly what i want into SUM of MAX Date(Dynamically instead of Hard coded value).  img_20200311.png

img_20200311.png

 I tried with following condition :- 

  sum(DAYSCLOSINGBALANCE*if(BALANCEDATE=aggr(nodistinct max(BALANCEDATE), FiscalYear),1,0))

  But after drilling down(FiscalYear) it not working. e.g ->FiscalYear -> Fiscal Month -> BalanceDate

 

 

Labels (4)
1 Reply
Lisa_P
Employee
Employee

Try this:
SUM({$<BALANCEDATE={'$(=MAX(BALANCEDATE))'}>} DAYSCLOSINGBALANCE)