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

    Help needed with formula

    Martijn Wanders

      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