14 Replies Latest reply: Nov 7, 2018 10:31 AM by Martijn Wanders

# Help needed with formula

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

• ###### Re: Help needed with formula

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

)

• ###### Re: Help needed with formula

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

• ###### Re: Help needed with formula

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?

• ###### Re: Help needed with formula

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.

• ###### Re: Help needed with formula

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

)

• ###### Re: Help needed with formula

Hi Sunny,

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

• ###### Re: Help needed with formula

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!

• ###### Re: Help needed with formula

Hi Sunny,

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

=IF(SecondaryDimensionality() = 2,

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

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

Maybe this help better

• ###### Re: Help needed with formula

I changed few more things which I forgot to highlight

=If(SecondaryDimensionality() = 2,

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

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

• ###### Re: Help needed with formula

Hi Sunny,

Ahhh, thank you for clarifying :-) I copied the whole expression and look at the attachment. Still januari 2018 will not start with the total of previous years.

• ###### Re: Help needed with formula

I am not sure what might be going on... do you think you would be able to share your application for us to troubleshoot it?

• ###### Re: Help needed with formula

Hi Sunny,

This is data from my customer and I can not put the file here. Do you have a email address?

• ###### Re: Help needed with formula

I have requested to follow you, once you approve my request, I can send you my email using PM.

• ###### Re: Help needed with formula

Thanks for connection and send you a transfer link :-)