topic Re: Running total with a condition in QlikView

Running total with a condition

bhaskarsharma03 — Wed, 27 Jun 2018 04:56:23 GMT

Hi,

I am trying to calculate running total based on a condition. In the attached sample file, I have taken a sample data for one Customer and one Company. What I want to check is if the Balance for one period is equal (or close) to the sales. If not then add the sales of previous period and continue till the cumulative sales is close to (but doesn't exceed) the Balance.

Here is one example for April (201804):

1. Is April Balance equal to April Sales?

2. If it is less, then compare April+March Sales with April Balance

3. If still less, then compare April+March+Feb Sales with April Balance

4. Continue till the running Sales total exceeds the Balance, and then take the running total of previous step (as that is the closest but not greater than the April Balance)

Thanks in advance for your help.

Regards,

Bhaskar

Re: Running total with a condition

bhaskarsharma03 — Wed, 27 Jun 2018 08:34:27 GMT

Any guidance... experts!!!

Re: Running total with a condition

sunny_talwar — Wed, 27 Jun 2018 12:12:51 GMT

Look at the attached image and qvw

Re: Running total with a condition

bhaskarsharma03 — Thu, 28 Jun 2018 04:06:19 GMT

Thank you Sunny for your help. The result is good for the month of April; however if you select other months, it doesn't give correct result. The calculation always seem to start from the period 201804 irrespective of which period is actually being selected.

This is what I get:

This is what it should be:

The calculation for Sales and Number of days should start from the selected period. eg if 201802 is selected, then the Balance for the period should be compared with Sales of 201802, and then keep on adding the sales of 201801,201712,... and so on.

Thanks again for your help.

Re: Running total with a condition

sunny_talwar — Thu, 28 Jun 2018 10:54:53 GMT

Fixed the expressions now

Re: Running total with a condition

bhaskarsharma03 — Thu, 28 Jun 2018 11:26:02 GMT

Thank you Sunny. This works great

Just wanted to know... can this be done at the back-end as well - may be five columns calculated at the script level !

Thanks again for your help!

Regards,

Bhaskar

Re: Running total with a condition

sunny_talwar — Thu, 28 Jun 2018 11:32:59 GMT

I think you might be able to do this in the script also

Re: Running total with a condition

bhaskarsharma03 — Thu, 28 Jun 2018 11:39:34 GMT

Can you please help, if that doesn't require too much of an effort.

Regards,

Bhaskar

Re: Running total with a condition

sunny_talwar — Thu, 28 Jun 2018 11:41:43 GMT

It will require some time and effort to come up with the script which does what you want.. I will try to work on it as and when I get some time

Re: Running total with a condition

sunny_talwar — Thu, 28 Jun 2018 13:04:21 GMT

Having looked at this a little more... it will be very challenging to do this in the script because this is mostly driven by selection. Is there a reason you are looking to do this in the script?

Re: Running total with a condition

bhaskarsharma03 — Thu, 28 Jun 2018 16:44:17 GMT

Considering that I have close to a billion records (if not more), I am trying to minimize the front end calculations as much as I can.