Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

how to use previous date in Set Analysis

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: how to use previous date in Set Analysis

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)


2 Replies
MVP
MVP

Re: how to use previous date in Set Analysis

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)


Not applicable

Re: how to use previous date in Set Analysis

Thank you very much.

Community Browser