topic Get Last Date & Previous Date from table in QlikView

Get Last Date & Previous Date from table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Get Last Date & Previous Date from table

sunny_talwar — Fri, 04 Mar 2016 12:14:44 GMT

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

Re: Get Last Date & Previous Date from table

Anonymous — Fri, 04 Mar 2016 12:14:49 GMT

simple try?

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

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

Re: Get Last Date & Previous Date from table

udit_kumar_sana — Fri, 04 Mar 2016 12:22:34 GMT

Hi,

You can try solving using Peek function.

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/InterRecordFunctions/Peek.htm?q=peek

Re: Get Last Date & Previous Date from table

Gabriel — Fri, 04 Mar 2016 14:33:11 GMT

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

10/21/2015, 10

1/15/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');