topic Re: Sum Conditional Set in QlikView

Sum Conditional Set

Anonymous — Wed, 15 May 2013 12:59:28 GMT

Hello Community,

I have a problem: I have a pivot table where the Dimensions are the following (in order):

Main Component (mainrawmat_cd)
Item (item_cd)

The report gets the information from a table where we have the movement of the items (purchase/sales/stock movement/etc).

Each row of the movement table has it's values according to the item, which could be the main component of other items.

My problem is that I want the report to have the quantity of items purchased for each Main Component, along with the quantity sold for each Item.

But the information table doesn't separate which items are Main Components. They are all Items. There is a column in the information table that shows the Main component of each item.

I tried the following expression: Sum( {$<doc_type={"Purchase"}, doc_status = {"OPEN"}, item_cd = {mainrawmat_cd}>} ( quantity - deliv_qt ) )

But it's still wrong...

Is it ok to compare 2 dimensions in the conditions?

Is there anyway to cancel the intrinsic condition of each row (where it sums for item_cd) and show the sum of any given condition? (in this case, where item_cd = mainrawmat_cd )

Thank you for your help!

Re: Sum Conditional Set

er_mohit — Wed, 15 May 2013 13:19:30 GMT

Sum( {1< item_cd = P({<doc_type={"Purchase"}, doc_status = {"OPEN"}>}[mainrawmat_cd])>} [quantity - deliv_qt] )

Re: Sum Conditional Set

Gysbert_Wassenaar — Wed, 15 May 2013 14:41:38 GMT

If item_cd is the primary key of your table then this should work:

Sum( {$<doc_type={"Purchase"}, doc_status = {"OPEN"}, item_cd = {'=item_cd=mainrawmat_cd'}>} ( quantity - deliv_qt ) )

If it isn't you could try creating a new field in the script: recno() as rowid and use that new field in the expression

Sum( {$<doc_type={"Purchase"}, doc_status = {"OPEN"}, rowid = {'=item_cd=mainrawmat_cd'}>} ( quantity - deliv_qt ) )

Otherwise you'll have to use an if statement for the intra-record comparison.

Re: Sum Conditional Set

Anonymous — Wed, 15 May 2013 15:09:27 GMT

Thank you for the replies, but they still don't fix my problem...

I'll give an example:

Item_cd = 11111 and item_cd = 22222 have mainrawmat_cd = 55555.
Item_cd = 55555 has mainrawmat_cd = 55555.
Item_cd 11111 has been sold 5 different times, let's say quantity = 20 for each time/row.
Item_cd 22222 has been sold 3 different times, let's say quantity = 15 for each time/row.
Item_cd 55555 (which is item_cd 11111's and item_cd 22222's mainrawmat_cd) has been purchased 2 times, let's say quantity = 40 and deliv_qt = 10.
*Remember these are 10 different rows in the information table.
*Item_cd is the primary key for this pivot table. In other words, the information is displayed according to item_cd.

Now on the pivot table, in the same row, I'd like to see:

Mainrawmat_cd item_cd quantity sold(for item_cd) quantity purchased(for mainrawmat_cd)

55555 11111 100 60

55555 22222 45 60

55555 55555 0 60

Is it a little bit clearer how the information is arranged?

When I use the suggested solutions mentioned above, they only work if i have mainrawmat_cd 55555 and item_cd 55555 on the same row (3rd row in the example). The other rows remain 0.

Thank you for your help!