Nested Query in an Object ?

[#1]

Dear all,

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

Hope QV 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 TOTAL REVENUE 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 TOTAL COSTS by CostGroup. Need to calculate the net numbers within a 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 with different transaction date. There are filters for users to select the transaction date periods. Thus, the calculation on REVENUE 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.

[#2]

Hi Herman QV,

     I am not very sure about the details of the calculations you want to have, but I think with the attached example you have some expressions like the ones you need, and you can easily customize to meet your requirements.

JG

[#3]

Hi JG,

    Thanks for your reply.

I had a look of your QVW and actually I had drafted what you have done before. But this is not what I want. The objective is to show the TOTAL COST by CostGroup if the corresponding Revenue is zero or missing, while Revenue is calculated by RevenueGroup. Since Revenue records do not come with a CostGroup value and thus, if I put CostGroup as the dimension, I don't know how to handle revenue records in this situation. I know that AGGR function could help but i tried many ways and just it doesn't work out.

Thanks