5 Replies Latest reply: Jun 12, 2013 8:41 AM by migle purzelyte RSS

    Expression working on a chart until I sort the dimension

    George Duckett

      I have the following expression:

       

      max({1<Year={$(#=vCurrentYear)}, MonthNumber={$(#=vCurrentMonth)}>} MonthlyTarget) // Monthly target
      *
      (
      Sum({1<TimeIsland.Year={$(#=vCurrentYear)}, TimeIsland.MonthNumber={$(#=vCurrentMonth)}, TimeIsland.DayNumber={"<=$(#=vCurrentDay)"}>}TimeIsland.IsShopDay_Int) // Days in month so far
      /
      Sum({1<TimeIsland.Year={$(#=vCurrentYear)}, TimeIsland.MonthNumber={$(#=vCurrentMonth)}>}TimeIsland.IsShopDay_Int) // Days in month
      )
      

       

      This gets the monthly target for the user, and multiplys it by the number of shop days we've had in the month, divided by the total shop days in the month.

       

      This works nicely providing I don't sort by my User dimension.

       

      If I sort by text A-Z by the user dimension it doesn't show that bar.

       

      I've norrowed it down to the Days in month so far and Days in month parts not getting calculated correctly.

       

       

      Has anyone encountered anything like this before? It seems very odd to me that sorting a dimension effects the result.

      I'm very new to QlikView so I'm probbaly missing something about how QlikView expressions work within charts etc.

       

      Thanks,

      George