topic Re: Sum based on limited occurences in QlikView

Sum based on limited occurences

Thu, 20 Mar 2014 09:59:30 GMT

Hi guys,

I should know this but I can't get it to work properly.

I want to sum sales data from today (vToday already calculated) until the 30th occurrence of a working days and only the when working days are equal below. Using the table below, the result should be 4099.

I use a normal calendar tables linked to a sales table. Something like:

Date	Working_days	Sales
17/03/2014	(Today)1	211
18/03/2014	1	21
19/03/2014	1	72
20/03/2014	1	147
21/03/2014	1	181
22/03/2014	0	15
23/03/2014	0	0
24/03/2014	1	157
25/03/2014	1	238
26/03/2014	0	0
27/03/2014	1	100
28/03/2014	1	42
29/03/2014	0	5
30/03/2014	0	209
31/03/2014	1	196
01/04/2014	1	42
02/04/2014	1	213
03/04/2014	1	248
04/04/2014	1	2
05/04/2014	0	4
06/04/2014	0	243
07/04/2014	1	216
08/04/2014	1	8
09/04/2014	1	212
10/04/2014	1	215
11/04/2014	1	21
12/04/2014	0	150
13/04/2014	0	91
14/04/2014	1	216
15/04/2014	1	8
16/04/2014	1	212
17/04/2014	1	215
18/04/2014	1	21
19/04/2014	0	150
20/04/2014	0	91
21/04/2014	1	196
22/04/2014	1	42
23/04/2014	1	213
24/04/2014	1	248
25/04/2014	1	2
26/04/2014	0	4
27/04/2014	0	243
28/04/2014	(30th occur.)1	196
29/04/2014	1	42
30/04/2014	1	213

Desired result = 4099

Can anyone help out?

Thanks in advance for your help.

Kind regards,

Nuno

Re: Sum based on limited occurences

amit_saini — Thu, 20 Mar 2014 10:05:47 GMT

Hi Nuno,

In load script like this,

Load

dual(date(Datefield, 'MMM-YYYY'), MonthEnd(Datefield)) as MonthYear,

From tablename;

Set the variable like

vStartYear = MonthStart(max(MonthYear),-9),

vEndYear = MonthEnd(max(MonthYear))

expression like this

Sum({<SalesDate_MonthYear={">=$(StartYear) <=$(EndYear)"}>} SalesNetPrice)

Note : Suppose if your data available for oct-2013 only so max date is oct-2013. so only the put the condition like MonthStart(max(MonthYear),-9).

Thanks,
AS

Re: Sum based on limited occurences

Thu, 20 Mar 2014 10:32:34 GMT

Thank you amit,

but that is how you do a simple range date calculation. Your answer does not cover summing by working days or limiting it to a give number of working days.

Kind regards,

Nuno

Re: Sum based on limited occurences

Thu, 20 Mar 2014 15:46:16 GMT

Please can anyone help?

Re: Sum based on limited occurences

Anonymous — Thu, 20 Mar 2014 19:33:19 GMT

Hi Nuno, i am also trying the solution for you. If you get any please post the solution. but sure i will try to get something for youu

Re: Sum based on limited occurences

Anonymous — Thu, 20 Mar 2014 19:35:49 GMT

Hi Nuno are you pulling these dates and working days from any database, it would be easy to do in database. Please let me know if that is the case

Re: Sum based on limited occurences

Thu, 20 Mar 2014 21:45:31 GMT

Hey Nuno,

When I did as per your requirement I got 4111 as the result. I didn't use today as that would be March 20.

Per your logic I added everything from 17/3/2013 to 28/4/2014 where Working_days =1

I created a few flags to find out the 30th day. It should be tweaked for today though.

Hope it helps

Thanks

Re: Sum based on limited occurences

maxgro — Thu, 20 Mar 2014 22:04:38 GMT

see attachment

Re: Sum based on limited occurences

Thu, 20 Mar 2014 22:12:12 GMT

Hi Vinay,

They are in a database but I don't have access. Neither the script should be modified.

The approach I'm looking for is through an expression like:

Sum({<Dater={">=$(=vToday0) <=$(=v30workdays)"}>} Sales)

The vWorkingDays is the variable I want to define...

Thank you so much for the help.

Kind regards,

Nuno

Re: Sum based on limited occurences

Thu, 20 Mar 2014 22:22:54 GMT

Thanks Massimo. Thanks everybody.

This could do the trick. I would prefer not to have script changes but they are minimal.

Kind regards,

Nuno