Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying do something with dates.
The problem is: I have two date fields and two value fields which I need to represent with 1 date. So I have sales and returns data and I need to be able to show sales and then reduce sales by the number of returns which happened in that week.
So in this table below from 11th - 15 Dec a person has made 6 sales. But I want to reduce this number by any returns made in a 60 day period. So Sales Net of returns would be 4 (You can see 4 returns made this week but only two which happened in the 60 day period).
I would prefer to code this in to the script with additional columns etc because of the volume of data and also I would like to have various date filter e.g. this week, last week, quarter etc.
So if I clicked this week date field, I would see a volume of 4.
Sale Date | Return Date | Sales | Return |
11/12/2017 | 1 | 0 | |
11/12/2017 | 1 | 0 | |
11/12/2017 | 1 | 0 | |
12/12/2017 | 1 | 0 | |
01/10/2017 | 13/12/2017 | 1 | 1 |
12/12/2017 | 13/12/2017 | 1 | 1 |
14/12/2017 | 1 | 0 | |
01/08/2017 | 14/12/2017 | 1 | 1 |
25/10/2017 | 12/12/2017 | 1 | 1 |
Any thoughts?
Thanks,
Kwok
Hi,
LOAD *, if(Return<>0, '1') as Flag, if(Return<>0 and [Sale Date]>= Date(today()-60), Sales) as Sales_60
.
.
find attached
Hi,
LOAD *, if(Return<>0, '1') as Flag, if(Return<>0 and [Sale Date]>= Date(today()-60), Sales) as Sales_60
.
.
find attached
You could try week(saledate) and week(returndate) in your load. That should match up the dates to a common week. Then you could have expressions such as sum(sales) or sum(returns). You're load could even has a statement such as Sales-Returns as Net Sales and your expression could just be sum(NetSales).
Interesting. The above looks like it should work for me, but I may give this a try also.
Hi,
Thanks Yousseff for the above, the other issue I have now is. I want to create a date field for this week and it to show the returns that happen this week and the sales.
If I was to select this week date fields, I would miss 1 of my returns (which happened in the week), if I was to include the 2nd return which happened this week, I would have a 1 value for a sale but I would not want it to show as a sale for this specific week.
I've used SET analysis to solve my above problem
Setting Variables as start & end date for sales and returns and then set analysis to sum between dates