topic Re: Sum up until dimension value in QlikView

Sum up until dimension value

Mon, 16 Sep 2013 12:51:28 GMT

Hello again...

I have a quite similiar problem to http://community.qlik.com/thread/62049.

But in my case it's a bit more complicated, having a master calendar attached and a second dimension.

To be more explicit, here it is:

Having a table ItemEntries:

ItemNo, Qty, Date

44444, 100, 24.01.2013

55555, 300, 24.01.2013

77777, 200, 24.01.2013

55555, 200, 25.01.2013

66666, 500, 25.01.2013

77777, 300, 25.01.2013

I want to sum up all items and their qty until the "masterdate". That means, I created a table (in load script by for loop):

MasterDate, ItemNo

24.01.2013, 44444

24.01.2013, 55555

24.01.2013, 66666

24.01.2013, 77777

25.01.2013, 44444

25.01.2013, 55555

25.01.2013, 66666

25.01.2013, 77777

The challange is now to get the sum until the MasterDate. And like in the post of Adrien the expression =Sum({$<[Date]={'<=$(MasterDate)'}>} Qty) doesn't seem to work (same values for each ItemNo).

The solution with accumulation wouldn't work here, if I see it right.

Any idea? I've tried some things in the load script, but it seems that I have not the correct idea...

Thanks a lot for your help!

Best regards

Andreas

Re: Sum up until dimension value

tresB — Mon, 16 Sep 2013 13:22:52 GMT

Is this what you want? PFA

Re: Sum up until dimension value

Mon, 16 Sep 2013 13:34:47 GMT

I'm afraid not. I see that it works quite well, but I'm looking for a straight table looking like:

MasterDate, ItemNo, Sum

24.01.2013, 44444, 100

24.01.2013, 55555, 300

24.01.2013, 66666, 0

24.01.2013, 77777, 200

25.01.2013, 44444, 100

25.01.2013, 55555, 500

25.01.2013, 66666, 500

25.01.2013, 77777, 500

But thanks for your idea with only(), I'll remember that for other future problems.

Best regards,

Andreas

Re: Re: Sum up until dimension value

tresB — Mon, 16 Sep 2013 13:40:13 GMT

PFA

Re: Sum up until dimension value

Mon, 16 Sep 2013 14:13:34 GMT

Oh good lord... how stupid am I?

Thanks!

Re: Sum up until dimension value

Wed, 18 Sep 2013 08:54:39 GMT

Having some million rows, it's very slow. Any possibility to use above() or something like that?

Re: Sum up until dimension value

tresB — Wed, 18 Sep 2013 09:03:57 GMT

You can create a flag in the script like:

Load

If(Date<=MasterDate, 'Y') as Flag

And then use the expression at the front end like:

Sum({<Flag={'Y'}>} Qty)

Re: Sum up until dimension value

Wed, 18 Sep 2013 09:43:57 GMT

Therefore I would need a Left Join, right?

Re: Sum up until dimension value

tresB — Wed, 18 Sep 2013 09:47:05 GMT

If you take the master date table first, then use a LEFT join, otherwise right. Or, even you can try apply map as well.