topic Re: Difference Between Maximum and Previous Date in QlikView

Difference Between Maximum and Previous Date

pandiarajan — Mon, 11 Sep 2017 18:32:47 GMT

Hi Team,

i would like to calculate date difference between Maximum and Previous maximum date by Customer

Input

Customer_ID	Customer_Name	Purchase_Date
1	Pandiarajan	2017-01-01
1	Pandiarajan	2017-01-15
1	Pandiarajan	2017-01-23
1	Pandiarajan	2017-02-10
2	Raja	2017-01-03
2	Raja	2017-01-12
2	Raja	2017-01-31

Output

Customer ID Customer Name Max_Date Second_Max_Date Difference

1 Pandiarajan 2017-02-10 2017-02-23 19

2 Raja 2017-01-31 2017-02-12 19

Thanks Lot

Re: Difference Between Maximum and Previous Date

sunny_talwar — Mon, 11 Sep 2017 18:38:35 GMT

Try like this

Table:

LOAD * INLINE [

Customer_ID, Customer_Name, Purchase_Date

1, Pandiarajan, 2017-01-01

1, Pandiarajan, 2017-01-15

1, Pandiarajan, 2017-01-23

1, Pandiarajan, 2017-02-10

2, Raja, 2017-01-03

2, Raja, 2017-01-12

2, Raja, 2017-01-31

];

OutputTable:

LOAD Customer_ID,

Date(Max(Purchase_Date)) as Max_Date,

Date(Max(Purchase_Date, 2)) as Second_Max_Date,

Max(Purchase_Date) - Max(Purchase_Date, 2) as Difference

Resident Table

Group By Customer_ID;

Re: Difference Between Maximum and Previous Date

vishsaggi — Mon, 11 Sep 2017 18:42:04 GMT

Try this:

Table1:

LOAD * INLINE [

Customer_ID, Customer_Name, Purchase_Date

1, Pandiarajan, 2017-01-01

1, Pandiarajan, 2017-01-15

1, Pandiarajan, 2017-01-23

1, Pandiarajan, 2017-02-10

2, Raja, 2017-01-03

2, Raja, 2017-01-12

2, Raja, 2017-01-31

];

NoConcatenate

Final:

LOAD Customer_ID,

Customer_Name,

Date(Max(Purchase_Date,1), 'YYYY-MM-DD') AS MaxPurchaseDate,

Date(Max(Purchase_Date,2), 'YYYY-MM-DD') AS [2nd MaxPurchaseDate]

Resident Table1

Group By Customer_ID, Customer_Name;

Drop Table Table1;

Use straight table with all the dimensions and

expr: = MaxDatePurchase - [2nd MaxPurchaseDate]

Re: Difference Between Maximum and Previous Date

its_anandrjs — Mon, 11 Sep 2017 18:44:09 GMT

In Backend script you required this or in the UI part i suggest do this in script part

Source:

LOAD Customer_ID, Customer_Name, Date(Date#(Purchase_Date,'YYYY-MM-DD'),'YYYY-MM-DD') AS Purchase_Date

Inline

[

Customer_ID, Customer_Name, Purchase_Date

1, Pandiarajan, 2017-01-01

1, Pandiarajan, 2017-01-15

1 ,Pandiarajan, 2017-01-23

1, Pandiarajan, 2017-02-10

2, Raja ,2017-01-03

2, Raja ,2017-01-12

2, Raja, 2017-01-31

];

FinalTable:

LOAD *,Max_Date - Second_Max_Date as Diff;

LOAD Customer_ID, Customer_Name, Date(Max(Purchase_Date)) AS Max_Date ,Date(max(Purchase_Date,2)) AS Second_Max_Date

Resident Source

Group By Customer_ID, Customer_Name;

DROP Table Source;

OutPut Yor Required:-

Re: Difference Between Maximum and Previous Date

pandiarajan — Mon, 11 Sep 2017 18:45:41 GMT

Thanks very much

Re: Difference Between Maximum and Previous Date

its_anandrjs — Mon, 11 Sep 2017 18:45:48 GMT

Find the attached also

Re: Difference Between Maximum and Previous Date

pandiarajan — Mon, 11 Sep 2017 18:45:52 GMT

thanks

Re: Difference Between Maximum and Previous Date

effinty2112 — Tue, 12 Sep 2017 10:29:45 GMT

Hi Pandiarajan,

This straight table seems to give you what you need:

Customer_ID	Customer_Name	Max(Purchase_Date)	Max(Purchase_Date,2)	Max(Purchase_Date)-Max(Purchase_Date,2)
1	Pandiarajan	2017-02-10	2017-01-23	18
2	Raja	2017-01-31	2017-01-12	19

cheers

Andrew