Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
alwayslearning
Creator
Creator

Date Problem

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 DateReturn DateSalesReturn
11/12/2017 10
11/12/2017 10
11/12/2017 10
12/12/2017 10
01/10/201713/12/201711
12/12/201713/12/201711
14/12/2017 10
01/08/201714/12/201711
25/10/201712/12/201711

Any thoughts?

Thanks,

Kwok

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

Hi,

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

.

.


find attached


View solution in original post

5 Replies
YoussefBelloum
Champion
Champion

Hi,

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

.

.


find attached


greend21
Creator III
Creator III

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
Creator
Creator
Author

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

alwayslearning
Creator
Creator
Author

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
Creator
Creator
Author

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