6 Replies Latest reply: Oct 30, 2012 4:50 PM by Celambarasan Adhimulam RSS

    Cumulative MTD?

      I am trying to calculate distinct users per date and distinct users MTD in a chart where the dimension is the date. Is this possible in QlikView?

      See attached Excel example.

       

      Thanks,

           -Simon-

        • Re: Cumulative MTD?
          Roberto Postma

          Is it a requirement that multiple months are visible (as in excel are january and february visible?)

          I think that could be hard in qlikview since then the cumulative part has to be "reset to 0" somehow when the month changes.

           

          I would set it up per month (and perhaps previous month or something). Then which month to view is just a selection/listbox.

          • Re: Cumulative MTD?
            Dalton Ruer

            I added a second dimension to a chart which was for test purposes just =MONTH(Date) just to get Jan, Feb etc to show. In the Presentation I just checked the Hide Column radio button so it didn't actually show

             

            Then I had 1 expression which was simply your DistinctUsers field.

             

            Then I had 2nd expression whish was the same thing but just your DistinctUsers field but I checked the "Accumulation" Radio button for "Full Accumulation" 

             

            You would need a more complicated 2nd dimension if you are cross years, or want do the same for quarters or whatever. But if you have those things as fields already then just use them as your "hidden" 2nd dimension.

            • Re: Cumulative MTD?

              I have a solution that works for me. Please see attached QVW.

               

              Summary: I generated a table that associates dates with a corresponding MTD date. So for 1/5/2012, I would have:

              Date, MTD_Date

              1/5/2012, 1/1/2012

              1/5/2012, 1/2/2012

              1/5/2012, 1/3/2012

              1/5/2012, 1/4/2012

              1/5/2012, 1/5/2012

               

              Using a table like this, I was able to calculate the DISTINCT number of users MTD for each day of each month.

               

              Hope this helps someone else!