topic Re: Get last date and previous one in QlikView

Get last date and previous one

Thu, 25 Sep 2014 10:36:20 GMT

Hello,

I am having trouble trying to solve the following:

I have a table with clients id and two columns, the first with the selected date (selected on the filter) and the column should include the date just previous of the selected one.

id_store	date 1	date 2
4	11/07/2014	-
4	11/08/2014	11/07/2014
4	11/09/2014	11/08/2014
If on my Qlik View I select the filter that contains the date, and I select 11/08/2014, what I want to obtain is the following:
id_store	date 1	date 2
4	11/08/2014	11/07/2014

Is this clear??

I hope you can help me!!

regards,

Re: Get last date and previous one

tcullinane — Thu, 25 Sep 2014 10:48:39 GMT

Not sure why you want this to display, you might be better having the previous date as a variable based on the selection if you mean to do other calculations based on it, but if you use a chart straight table you can have the third column as an expression :

=[date1]-1

Re: Get last date and previous one

Thu, 25 Sep 2014 10:56:21 GMT

HI, I mean to do some calculus. In summary what I want is the following:

store

products sold on date 1

product sold on date 2

Date 1 being the selcted one, and date two being the previous.

The catch on this is also that if I have more than two dates and i SElect the last, and just the previous dint sold the product, I need to pick the last date that this store sold the product without being the selected one.

It is confusing to explain, so please let me know if you understand.

Re: Get last date and previous one

jagan — Thu, 25 Sep 2014 11:01:20 GMT

Hi,

Try this in script

LOAD

If(Previous(id_store) = id_store, Previous(date1)) AS date2

FROM DataSource

order by id_store;

Now use date2 column in chart.

Regards,

jagan.

Re: Get last date and previous one

tcullinane — Thu, 25 Sep 2014 11:24:23 GMT

It will depend on how your data is loaded alright, If you are loading a table with [Store_ID,Sale_Date,Product,Amount,etc,...] an expression such as:

above(Sales_Date) with set analysis or if statement to limit the store and product should work.