Help needed with formula

[mwanders]

Hi all,

I have a formula just like below. It's calculating a cumulative sum in a pivot table. This formule works well if a expand month and year, see screenshot "Cumulative with field year expanded".

num ( rangesum ( before ( total sum ({1 < [administration name] = P ([administration name]) , [report type] = {'B'}  > } [Saldo] ), 0 , column (total))), '€ #.##0')

If I want to expand only year 2018 than the previous year will be showned as 0,  see screenshot "Cumulative field year not expanded".

What I want: if previous years are not expanded then show the cumulative total of previous year. And jan. of this year will start with the cumulative total of previous year.

I read some Qlik threads and see some formulas with aggr and sum. I tried to implement this but no succes.

Can anybody help me out with this question?

Thank you very much!

Greeting Martijn Wanders

[sunny_talwar]

May be try using SecondaryDimensionality here

If(SecondaryDimensionality() = 2,

Num(RangeSum(Before(TOTAL Sum({1<[administration name] = P([administration name]), [report type] = {'B'}>} [Saldo]), 0, Column(TOTAL))), '€ #.##0'),

Num(Sum({1<[administration name] = P([administration name]), [report type] = {'B'}>} [Saldo]), '€ #.##0')

)

[mwanders]

Hi Sunny,

Thank you for your reply and this help a lot. For the previous year the total is summed and I can expand this.

If you look at the attachment "screenshot" you will see that 2018 starts over. January 2018 must be started with the total of 2017 + mutations in January 2018 and then over the following month cumulative.

Do you have an idea to tackle this?

Greetz Martijn

[sunny_talwar]

Do you have a month year field in your dashboard we can use? Also, what is the name of your 1st dimension in the chart?

[mwanders]

Hi Sunny,

The first dimension is called "Indeling".

I have a year-month field but then my customer can not collaps and expand the pivot on year level.

[sunny_talwar]

You don't need to add year-month as a dimension... I just need it for the expression

If(SecondaryDimensionality() = 2,

Num(Aggr(RangeSum(Above(Sum({1<[administration name] = P([administration name]), [report type] = {'B'}>} [Saldo]), 0, RowNo())), Indeling, ([year-month], (NUMERIC))), '€ #.##0'),

Num(Sum({1<[administration name] = P([administration name]), [report type] = {'B'}>} [Saldo]), '€ #.##0')

)

[mwanders]

Hi Sunny,

What is (NUMERIC)? If I use this QlikSense does not reconize it... Or do I have to fill something else?

[sunny_talwar]

The syntax editor doesn't recognize it, but do you see a value when you apply the same expression? Look here for info on the usage of (NUMERIC) here

The sortable Aggr function is finally here!

[mwanders]

Hi Sunny,

If I apply the expression then zeros are showned for 2018. Below the copied expression from my dashboard:

=IF(SecondaryDimensionality() = 2,

num(AGGR(rangesum(before(TOTAL sum({1<[Adminstratie naam]=P([Adminstratie naam]),[Rapport type B/R]={'B'}>}

[Periode saldo omgerekend naar €]),0,ColumnNo(TOTAL))),[Rapport layout beschrijving],([Boekjaar-periode],(NUMERIC))),'€ #.##0'),

    num(sum({1<[Adminstratie naam]=P([Adminstratie naam]),[Rapport type B/R]={'B'}>}

[Periode saldo omgerekend naar €]),'€ #.##0'))

Maybe this help better

[sunny_talwar]

I changed few more things which I forgot to highlight

=If(SecondaryDimensionality() = 2,

num(AGGR(rangesum(Above(Sum({1<[Adminstratie naam]=P([Adminstratie naam]),[Rapport type B/R]={'B'}>}

[Periode saldo omgerekend naar €]),0,RowNo())),[Rapport layout beschrijving],([Boekjaar-periode],(NUMERIC))),'€ #.##0'),

    num(sum({1<[Adminstratie naam]=P([Adminstratie naam]),[Rapport type B/R]={'B'}>}

[Periode saldo omgerekend naar €]),'€ #.##0'))