# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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_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

Tags (2)
7 Replies
MVP

## Re: Difference Between Maximum and Previous Date

Try like this

Table:

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:

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;

Esteemed Contributor III

## Re: Difference Between Maximum and Previous Date

Try this:

Table1:

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:

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]

MVP

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

Contributor

Thanks very much

MVP

## Re: Difference Between Maximum and Previous Date

Find the attached also

Contributor

## Re: Difference Between Maximum and Previous Date

thanks

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