Sum up until dimension value

[#1]

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

[tresesco]

Is this what you want? PFA

[#3]

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

[tresesco · Accepted solution]

PFA

[#5]

Oh good lord... how stupid am I?

Thanks!

[#6]

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

[tresesco]

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)

[#8]

Therefore I would need a Left Join, right?

[tresesco]

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