0 Replies Latest reply: Jan 19, 2016 6:43 PM by Michael Theophilou RSS

    Assistance with Cumulative Count in QlikSense

      Hi, I have been trying to create line chart showing a count of the number of items of a particular status on a given day. I have been using rangecount and rangesum to try to achieve this with no luck.

       

      Basically, i have a set of items (Id)  which have a StatusCode (could be either A, C, U F...) . The items could have a 'NULL' or '00:00:00' value for TDate and also have a date created (%_Date)

       

      IdStatusCodeTDate%_Date
      130F00:00.04/06/2015
      149FNULL5/06/2015
      173F00:00.05/06/2015
      185F00:00.05/06/2015
      237FNULL8/06/2015
      8006FNULL18/01/2016
      8065FNULL19/01/2016
      8071FNULL19/01/2016

      I would like to count the number of unique items (Id) with an F StatusCode which were created (%_Date) on the day with a NULL TDate, for the last 30 days only

       

      I have been using the expressions as follows;

      Dimension:

      if((Today()-[xxx.%_Date])<=30, [xxx.%_Date])

      Measure:

      RangeCount (Above(Count(Distinct if(xxx.StatusCode='F'and IsNull(xxx.TDate), [xxx.Id])),0,rowno()))

       

       

      I am currently having two issues depending on which calcs i use. Either the date values are now unique, thus i will have two or three of the same date with one item attributed to each, OR i the graph will display a correct incline BUT the first date will have a value of one where it should actually have a value of 40 and accumulate from there.

       

      Any help would e greatly appreciated.

       

      thanks

       

      Michael