topic Re: Sum IF, from previous available date in QlikView

Sum IF, from previous available date

Tue, 24 Feb 2015 09:04:16 GMT

Hi,

Days with

pmt_amount<>0

but

credit line connected pmts = 0

are not working correctly.

I changed the master IF-statements from

If(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)) <> 0,

If(sum({<Date = >}Pmt_amount) <> 0,

now this happens: If "Sum Date" shows zero, then "Sum previous" should be showing zero the next day.

Pls find example file attached

Re: Sum IF, from previous available date

sunny_talwar — Tue, 24 Feb 2015 10:38:13 GMT

So let me understand this a little bit. You mean that there could be places (for example 2015/01/07) which could have 0 now for Sum Previous expression?

Re: Sum IF, from previous available date

sunny_talwar — Tue, 24 Feb 2015 10:48:40 GMT

So what I think you are trying to say is that the credit line connected pmts can be 0, but the overall pmt_amt should not be 0? correct???

Changing the master alone will not help. all the if statements underneath the master will have to change.

That will be a lot of rework.I will see if I can resolve it for you.

Best,

Re: Sum IF, from previous available date

Tue, 24 Feb 2015 10:49:36 GMT

Yes that is correct. Maybe I was a bit unclear before.

Open days with no "Credit line connected payments" do exist

It happens rarely but it does happen. Which means sometime Sum Previous could be 0.

Problem was that in the example data there were no such days.

So what Im trying to say is that for all open days, all we know for sure is that the below statement is true:

Sum(Pmt_Amount) <>0

Re: Sum IF, from previous available date

Tue, 24 Feb 2015 10:50:28 GMT

Sorry for the inconvenience caused

Re: Sum IF, from previous available date

hariprasadqv — Tue, 24 Feb 2015 10:52:48 GMT

suppress Null values may give you a solution.

Re: Sum IF, from previous available date

sunny_talwar — Tue, 24 Feb 2015 10:55:06 GMT

Took way less time then I thought it would take. I am guessing this is what you want. PFA

Best,

Re: Sum IF, from previous available date

sunny_talwar — Tue, 24 Feb 2015 10:59:12 GMT

No inconvenience brother, I am glad that I am able to help + its a learning experience for me also. I love these daily quest I run into on the community. The more I help, the more I learn.

Best,

Re: Sum IF, from previous available date

Tue, 24 Feb 2015 11:06:10 GMT

A true work of art, thank you very much!

Hopefully this is it!

Re: Sum IF, from previous available date

sunny_talwar — Tue, 24 Feb 2015 15:47:23 GMT

Not a problem. Always there to help.

Best,