Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
simple try?
Last Date: Date(max(TransDate),'DD/MM/YYYY')
Last Previous Date: Date(max(TransDate)-1,'DD/MM/YYYY')
Hi,
You can try solving using Peek function.
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');