Get Last Date & Previous Date from table

[Anonymous]

Hi Qlikview Team

I have table as below and i have show last date and previous date sales values in two text boxes. When  i create formula max(TransDate) it shows last date sales but when i make formula max(TransDate)-1 it shows date as "03/03/2016" but i want sales of date "1/15/2016" . I have a filter of TransDate in my report when user make selection it has to change accordingly

Please help me to fix it

TransDate	Sales
5/10/2014	10
9/29/2014	10
9/29/2014	10
9/29/2014	10
9/29/2014	10
10/21/2015	10
10/21/2015	10
10/21/2015	10
10/21/2015	10
1/15/2016	10
1/15/2016	10
3/4/2016	10
3/4/2016	10
3/4/2016	10

[sunny_talwar]

Try this:

Max(TransDate, 2)

UPDATE: To avoid the filters to not negatively impact the second max date, you will need a set analysis to ignore TransDate. So try this:

Max({<TransDate>} TransDate, 2)

UPDATE: Disregard the above two expressions. Try this one:

=Date(If(GetSelectedCount(TransDate) = 1, Aggr(Above(Max({<TransDate>} TransDate)), TransDate), Max({<TransDate>} TransDate, 2)))

Attaching a sample

[Anonymous]

simple try?

Last Date:  Date(max(TransDate),'DD/MM/YYYY')

Last Previous Date: Date(max(TransDate)-1,'DD/MM/YYYY')

[udit_kumar_sana]

Hi,

You can try solving using Peek function.

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/InterRecordFunctions/P...

[Gabriel]

Hi,

I don't know what approach you prefer but I think you can do this in the script

TransDate:

LOAD * INLINE [

    TransDate, Sales

    5/10/2014, 10

    9/29/2014, 10

    9/29/2014, 10

    9/29/2014, 10

    9/29/2014, 10

    10/21/2015, 10

    10/21/2015, 10

    10/21/2015, 10

    10/21/2015, 10

    1/15/2016, 10

    1/15/2016, 10

    3/4/2016, 10

    3/4/2016, 10

    3/4/2016, 10

];

DIstDate:

LOAD

Distinct

count(TransDate)        AS DisCount

,TransDate                AS DistTansDate

Resident TransDate

Group By TransDate

Order BY TransDate ASC

;

LET vMaxPreviousDate = Peek('DistTansDate',-2,'DIstDate');

LET vMaxDate = Peek('DistTansDate',-1,'DIstDate');