Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
)
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
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?
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.
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')
)
Hi Sunny,
What is (NUMERIC)? If I use this QlikSense does not reconize it... Or do I have to fill something else?
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
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
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'))