topic Re: changing calculation for Partial Sums using SecondaryDimentionality in QlikView

changing calculation for Partial Sums using SecondaryDimentionality

Emil_bros — Thu, 06 Jun 2019 08:20:38 GMT

Hello Colleagues,

I need to change the logic of partial sums calculation in pivot table. For year quartes, instead of sum of 3 month, I need the last month value to be presented as quarter total. I use SecondaryDimentionality but it won't work properly'

In given example, instead of 19730 (sum of months) I want to get 6317 (last month val)

To solve this I use secondary dimentionality to locate on which level of cumulation given section is.

After that I use Max(Month) to check if last month of the quarter is properly defined for quarter total column - and it works - for Q1 I get 06-2019, for Q2 I get 09-2019.

then I use Max(Month) in final formula:

if(
SecondaryDimensionality() = 1,
sum(Stock),
sum( {<Month = {'$(=Max(Month))'}>} Stock)
)

Sadly the result is correct only for highest quarter . In example below - correct for Q2 and total overall, incorrect for Q1. It's strange because Max(Month) correctly assigned 06-2019 as last month of Q1.

Any ideas where I went wrong?

Best Regards,

Emil

Re: changing calculation for Partial Sums using SecondaryDimentionality

sunny_talwar — Thu, 06 Jun 2019 11:46:42 GMT

Try doing this using Aggr() function...

If( SecondaryDimensionality() = 1, Sum(Stock), Sum(Aggr(If(GPSI_Month = Max(TOTAL <GPSI_QUARTER> GPSI_Month), Sum(Stock)), GPSI_QUARTER, GPSI_Month)) )

You might be able to use FirstSortedValue() function, but I would need a sample to test it out.

Re: changing calculation for Partial Sums using SecondaryDimentionality

Emil_bros — Thu, 06 Jun 2019 12:14:54 GMT

Thank you very much Sunny, this works perfectly!