Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Sum based on limited occurences

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:

DateWorking_daysSales
17/03/2014(Today)1211
18/03/2014121
19/03/2014172
20/03/20141147
21/03/20141181
22/03/2014015
23/03/201400
24/03/20141157
25/03/20141238
26/03/201400
27/03/20141100
28/03/2014142
29/03/201405
30/03/20140209
31/03/20141196
01/04/2014142
02/04/20141213
03/04/20141248
04/04/201412
05/04/201404
06/04/20140243
07/04/20141216
08/04/201418
09/04/20141212
10/04/20141215
11/04/2014121
12/04/20140150
13/04/2014091
14/04/20141216
15/04/201418
16/04/20141212
17/04/20141215
18/04/2014121
19/04/20140150
20/04/2014091
21/04/20141196
22/04/2014142
23/04/20141213
24/04/20141248
25/04/201412
26/04/201404
27/04/20140243
28/04/2014(30th occur.)1196
29/04/2014142
30/04/20141213

Desired result = 4099

Can anyone help out?

Thanks in advance for your help.

Kind regards,

Nuno

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Sum based on limited occurences

see attachment

9 Replies
amit_saini
Honored Contributor III

Re: Sum based on limited occurences

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

Not applicable

Re: Sum based on limited occurences

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

Not applicable

Re: Sum based on limited occurences

Please can anyone help?

vinay_bangari
Valued Contributor III

Re: Sum based on limited occurences

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

vinay_bangari
Valued Contributor III

Re: Sum based on limited occurences

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

Not applicable

Re: Sum based on limited occurences

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

AJ

MVP
MVP

Re: Sum based on limited occurences

see attachment

Not applicable

Re: Sum based on limited occurences

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


Not applicable

Re: Sum based on limited occurences

Thanks Massimo. Thanks everybody.

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

Kind regards,

Nuno

Community Browser