topic Re: Master Calendar + Previous Day Balance in QlikView

Master Calendar + Previous Day Balance

Wed, 23 Dec 2015 07:40:57 GMT

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.

Regards,

Usama Anwar

Re: Master Calendar + Previous Day Balance

Gysbert_Wassenaar — Wed, 23 Dec 2015 18:16:34 GMT

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')

Re: Master Calendar + Previous Day Balance

Mon, 28 Dec 2015 06:37:09 GMT

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')