Date Problem

[alwayslearning]

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

[YoussefBelloum · Accepted solution]

Hi,

LOAD *, if(Return<>0, '1') as Flag, if(Return<>0 and [Sale Date]>= Date(today()-60), Sales) as Sales_60

.

.

find attached

[greend21]

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).

[alwayslearning]

Interesting.  The above looks like it should work for me, but I may give this a try also.

[alwayslearning]

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.

[alwayslearning]

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