topic Re: Aggregate figures by month in QlikView

Aggregate figures by month

datanibbler — Thu, 07 Aug 2014 08:18:22 GMT

Hi,

in all of my apps I have one chart with shows the presence_quota, based on figures from our personell_database.

- There is a binary field (0 or 1) for every employee who is associated with a certain area;

- By just summing up that field, I get the correct nr. of employees every day.

<=> The problem is when I change the dimension from "day" to "month" (a standard feature of all my charts) and thus I need to
aggregate the data.

- Currently I have the following formula:

>>> aggr(sum({$<%Datum = {"$(= '<' & DATE(Today()))"}>}Anz_MA_soll), Monat)) <<<

(I don't want this data for "Today" because other data is not yet available, so it would not match and result in incorrect figures)

That, however, gives me gigantic figures. I just tried out dividing it by 28 - roughly the nr. of days in a month - and that results in plausible figures.

The issue - and the question - is, how can I build that into the expression since every month has a different nr. of days (and, even more so, working_days)

Thanks a lot!

Best regards,

DataNibbler

Re: Aggregate figures by month

kaushiknsolanki — Thu, 07 Aug 2014 08:25:23 GMT

HI,

You can create an inline table with months and days, just like below.

Load * inline [

Month, Days

Jan, 31

Feb, 28

March, 31

......

];

Now link this table with your Month Fields in the data model.

In chart if you have the month as dimension, you can directly divide it by days.

Sum(Monat)/Sum(Distinct Days)

Regards,

Kaushik Solanki

Re: Aggregate figures by month

datanibbler — Thu, 07 Aug 2014 08:33:53 GMT

Hi Kaushik,

I have a master_calendar with two fields which could be of use here:

- "Monatstag" (day_of_month) - that goes up from 1 to whatever, it is 7 for today

- "Monatstage" (days_in_month) - that is 31 for today

Could I use those? - keeping in mind that the aggregated figure for August is already on display today, but dividing by 31 would result in minuscule figures, of course...

Thanks a lot!

Best regards,

DataNibbler

Re: Aggregate figures by month

kaushiknsolanki — Thu, 07 Aug 2014 08:35:52 GMT

Hi,

Then you will have to use the max instead of sum.

Regards,

Kaushik Solanki

Re: Aggregate figures by month

datanibbler — Thu, 07 Aug 2014 08:59:23 GMT

Hi Kaushik,

I just realized that is not quite going to work - the field "Monatstag" is a static field, not a function - so that only works with one day selected. Well, I can calculate it, it's just for the current month.

What do you mean with using max instead of sum?

Best regards,

Moritz 😉

Re: Aggregate figures by month

kaushiknsolanki — Thu, 07 Aug 2014 09:04:13 GMT

I mean, in the expression which i had suggested, use the Max(Distinct Day) instead of Sum.

Regards,

Kaushik Solanki

Re: Aggregate figures by month

datanibbler — Thu, 07 Aug 2014 09:19:13 GMT

I don't understand - never mind. I made it now, I think - for all the past months, the field from my master_calendar is good. The challenge is only the current month - and there I can use

>>> (num(Today() - Monthstart(Today()))+1) <<<

That will give me a 7 for today, for example, and so the figure is plausible - I'd say it is correct, namely 35, about the same that I have in the other view, with the dimension "day".

I will close the thread.

Thanks a lot!

Best regards,

DataNibbler