Get closing balance of previous month as opening balance for current month and so on..

[puneetagarwal]

Hi All,

I have a scenario where in I have to get closing balance of previous month as opening balance for current month...
Ex: Apr closing balance is 2000 and May Closing balance is 3000 and so on..
So the opening balance for May should be 2000 and June should be 3000 and so on..

Can someone help me with this?

Note: I do not want to use ABOVE function as it has restrictions which doesn't meets my requirements. Apart from that I have also tried the below set analysis but it returns the value only for the particular month not all the months:

sum({<Month={'$(=Month(addmonths(max(Date),-1)))'}>}Sales)

Thanks in advance

Regards,
Puneet

[sunny_talwar]

For multiple months, set analysis won't work.... you will either have to use Above() or AsOfTable... What restrictions did you run into using Above() function?

[puneetagarwal]

Hi Sunny,

Glad you are available. If I used above function then when I try to select one particular value from the filter pane the charts shows 0 value for that measure. because after selecting it picks only 1 month and above function picks from the previous month.

If we select 2 values in month column then we will get correct value for 2nd month.

Can you please tel me how to use AsOfTable?

Regards,

Puneet

[sunny_talwar]

What was the exact expression you use for Above? You can try something like this....

Assuming that the original expression looked like this

Above(Sum(Measure))

then change it to

Above(Sum({<Month>}Measure)) * Avg(1)

[puneetagarwal]

I have already the used the expression you have suggested Sunny, but as I mentioned earlier if we use ABOVE function on selection from the field the charts gets disappear if we select any particular one month

[sunny_talwar · Accepted solution]

If you ignore the selection field within your expression and multiply by Avg(1)... it should not do that... just like shown here   -> above() function and date selection

[puneetagarwal]

Thanks A lot sunny that really worked for me 🙂 Thanks a ton.

One doubt , as you have mentioned in the below post I was missing 2 key things

Above(Sum({<Month>}Measure)) * Avg(1)

I was missing writing only month field and Avg(1).

Can you please explain how this avg(1) works and upon selection why this is not going.

Regards,

Puneet

[sunny_talwar · Accepted solution]

I am not sure I understand your question?

Can you please explain how this avg(1) works and upon selection why this is not going.

[puneetagarwal]

Hi Sunny,

I got the answer for the above question. apologies for late reply. Can you please add your views/comments on below question?

https://community.qlik.com/t5/New-to-Qlik-Sense/measures-as-a-dimension-in-bar-chart/m-p/1614325#M14...
Thanks in Advacnce

Regards,
Puneet Agarwal

[puneetagarwal]

Hi Sunny,

Just wanted to understand what is the reason behind including Avg() function in the below expression.

Regards,
Puneet Agarwal