HELP - Summarized table calculations based on 2 different groupings

Thu, 24 Jan 2013 10:02:59 GMT

Dear all,

I encountered a very complicated problem and I am stucked. The concept sounds simple but I don't know how to handle in Qlikview.

Hope experts here could help me to resolve this..

Thanks in advance.

I will try my best to describe the situation. I will break down the logic into steps.

Below is the sample data set:

First of all, need to calculate the revenue group by ShipmentNumber and RevenueGroup and the result as below:

I have to show all the Costs that do not have revenue in the RevenueGroup and show the costs into columns called "Negative" and "Positive". Result as below:

After the above, the final result has to show the costs group by CostGroup. Need to calculate the net numbers within the ShipmentNumber of the same CostGroup. Then add up all the deduced "Negative" or "Positive" number with all the ShipmentNumber.

The expected final result as below:

Please help.

MANY MANY THANKS!!

One more thing need to mention. I CANNOT PRE-CALCULATE the revenue in the scripts because each row in the data set is a transaction input by users in different transaction date. There are filters for users to select the transaction date periods. Thus, the calculation result will vary based on the selection and so, all the calculations have to be done on the fly.

Attached is the data of the above in Excel file.

topic HELP - Summarized table calculations based on 2 different groupings in QlikView

HELP - Summarized table calculations based on 2 different groupings