topic Re: Cumulative MTD? in QlikView

Cumulative MTD?

Sun, 28 Oct 2012 20:18:32 GMT

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?

Sun, 28 Oct 2012 20:26:26 GMT

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?

Mon, 29 Oct 2012 18:47:14 GMT

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?

Tue, 30 Oct 2012 13:49:55 GMT

Hey Roberto,

Thanks for the response. Yes, the requirement is to show multiple months. You can see how I'm having a hard time with this one...

Re: Cumulative MTD?

Tue, 30 Oct 2012 20:07:11 GMT

Dalton,

Thanks for the response. Using Full Accumulation won't work because it will only add the values from the above rows together. I need to know DISTINCT users, MTD for each day of each month.

Re: Cumulative MTD?

Tue, 30 Oct 2012 20:10:40 GMT

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!

Re: Cumulative MTD?

CELAMBARASAN — Tue, 30 Oct 2012 20:50:13 GMT

Hi,

Check the Simplified solution of your concept.

Attached: