topic Re: Date Problem in QlikView

Date Problem

alwayslearning — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Date Problem

YoussefBelloum — Fri, 15 Dec 2017 13:11:31 GMT

Hi,

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

find attached

Re: Date Problem

greend21 — Fri, 15 Dec 2017 13:25:56 GMT

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

Re: Date Problem

alwayslearning — Tue, 19 Dec 2017 10:07:13 GMT

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

Re: Date Problem

alwayslearning — Tue, 19 Dec 2017 17:18:47 GMT

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.

Re: Date Problem

alwayslearning — Wed, 20 Dec 2017 10:21:18 GMT

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