Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
pandiarajan
Contributor

Difference Between Maximum and Previous Date

Hi Team,

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

Input

  

Customer_IDCustomer_NamePurchase_Date
1Pandiarajan2017-01-01
1Pandiarajan2017-01-15
1Pandiarajan2017-01-23
1Pandiarajan2017-02-10
2Raja2017-01-03
2Raja2017-01-12
2Raja2017-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

7 Replies

Re: Difference Between Maximum and Previous Date

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;

vishsaggi
Esteemed Contributor III

Re: Difference Between Maximum and Previous Date

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]

Capture.PNG

Re: Difference Between Maximum and Previous Date

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:-

Opp3.PNG

pandiarajan
Contributor

Re: Difference Between Maximum and Previous Date

Thanks very much

Re: Difference Between Maximum and Previous Date

Find the attached also

pandiarajan
Contributor

Re: Difference Between Maximum and Previous Date

thanks

effinty2112
Honored Contributor

Re: Difference Between Maximum and Previous Date

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)
1Pandiarajan2017-02-102017-01-2318
2Raja2017-01-312017-01-1219

cheers

Andrew