0 Replies Latest reply: Jul 24, 2014 9:48 AM by Patrick Baal RSS

    Startvalue and cumulative

      Hi all,

       

      Hopefully someone can help with the challenge I'm facing.

       

      I created a dynamic pivot table. This pivot table exists of two dimension. A brand or other dimension related to the brand and a time dimension being a year, month, week oor day. These dimensions can be turned on or of by a listbox with the dimensions in it.

       

      the data can look like this:

      month 1,2 and 3 also has got amounts in it!

      Brand
      monthstartexpression1
      expression2total of start +  expr1 + expr2
      brand14sum of previous month(s)sum of amount1 this monthsum of amount2 this monthtotal of previous 3 columns
      5previous total at the rightsum of amount1 this month

      sum of amount2 this month

      total of previous 3 columns
      6previous total at right sidesum of amount1 this monthsum of amount2 this monthtotal of previous 3 columns

       

      the expression used for start is:

      if    (

              RowNo() = 1,

              sum(aggr(sum({$<TransactionDay = {"<$(vStartDate)"}>} total <$(vDimension)> Amount),$(vDimension) )), 

              above([Floatwaarde end])

          )

       

      vDimension is declared in the Variable Overview:

      =subfield(GetFieldSelections( Listbox with dimensions),',',1)

       

      However: if there is no data in month 5 QlikView cannot continue aggregating the first column().

      Can anyone please help finding a solution for my expression. Would be great.

       

      Thanks in advance,