Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Master Calendar + Previous Day Balance

Hi Community,

I have a small problem when using the master calendar in QlikView. In the database, I have one date field, 'update_date_cps_full'. I have used this field in the master calendar. In Set analysis, I want the latest date balance and previous date balance. Since Sunday is not a working day, I are removed it from my calendar.

My expression (mentioned below) works fine for current date balance and previous date balance when there is no selection of date, but whenever I select a date in history, the current balance shows correctly but the previous day balance disappears.

Current Date Expression: =

Num(sum({<UPDATE_DATE_CPS_FULL={'$(=MAX(UPDATE_DATE_CPS_FULL))'},Type ={'Financing - Funded'},FLAG = {'CORP'}>}BALANCE_CPS_FULL)/1000000,'#,##0')

Previous Date Expression: =

=Num(sum({<UPDATE_DATE_CPS_FULL={'$(=MAX(UPDATE_DATE_CPS_FULL,2))'},Type ={'Financing - Funded'},FLAG = {'CORP'}>}BALANCE_CPS_FULL)/1000000,'#,##0')

As you can see, I use Max(Date) for current balance and Max(Date,2) from second maximum date which becomes the previous day.

On selection of any date and month, the previous day becomes 0.

Any advice on this issue or is there any other method that I could, perhaps making a variable for previous day balance.


Usama Anwar

2 Replies

If you select one UPDATE_DATE_CPS_FULL value there's only one maximum value, i.e. the value you selected. There is no second highest value possible in your selection. Try this expression:

=Num(sum({<UPDATE_DATE_CPS_FULL={'$(=MAX({<UPDATE_DATE_CPS_FULL={'<$(=MAX(UPDATE_DATE_CPS_FULL))'}>}UPDATE_DATE_CPS_FULL))'},Type ={'Financing - Funded'},FLAG = {'CORP'}>}BALANCE_CPS_FULL)/1000000,'#,##0')

talk is cheap, supply exceeds demand
Not applicable

It gives a red under line in the bold area below and upon selection of any date in calendar it does not work.

The value is 0 whenever I select a date in history.

=Num(sum({<UPDATE_DATE_CPS_FULL={'$(=MAX({<UPDATE_DATE_CPS_FULL={'<$(=MAX(UPDATE_DATE_CPS_FULL))'}>}UPDATE_DATE_CPS_FULL))'},Type ={'Financing - Funded'},FLAG = {'CORP'}>}BALANCE_CPS_FULL)/1000000,'#,##0')