Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
see attachment
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
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
Please can anyone help?
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
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
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
see attachment
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
Thanks Massimo. Thanks everybody.
This could do the trick. I would prefer not to have script changes but they are minimal.
Kind regards,
Nuno