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