how to use previous date in Set Analysis

[#1]

Hi All,

I am facing a small challenge in set analysis.

I have loaded three dates from Data ware house in QlikView,

- 30-Sep.

- 31-Aug

- 31-Jul

I want a comparative analysis in a chart. By using set analysis, I got the latest (MAX) date balance i-e- 30-Sep and by using MIN function I got the 31-JUL balances.

Syntax for both are as follows:

For September:

=sum({<UPDATE_DATE={'$(=MAX(UPDATE_DATE))'},Deposit_Product_Nature ={'CURRENT ACCOUNT'}>}AMOUNT_LCY)

For July:

=sum({<UPDATE_DATE={'$(=MIN(UPDATE_DATE))'},Deposit_Product_Nature ={'CURRENT ACCOUNT'}>}AMOUNT_LCY)

What the syntax would be to present the 31-AUG balances?

[tresesco · Accepted solution]

If your expressions are working fine with those date values, for this specific date set, you could get 31-AUG as second-maximum date, and hence can try like:

=sum({<UPDATE_DATE={'$(=MAX(UPDATE_DATE, 2))'},Deposit_Product_Nature ={'CURRENT ACCOUNT'}>}AMOUNT_LCY)

If you have many dates, you might want this date as last-month-end date, you could try like:

=sum({<UPDATE_DATE={'$(=MonthEnd(MAX(UPDATE_DATE),-1))'},Deposit_Product_Nature ={'CURRENT ACCOUNT'}>}AMOUNT_LCY)

[#3]

Thank you very much.