Sum from different field with criteria

[#1]

Hello

I have a problem with the data I get, I can't change it.

Table is like this:

• DBFIncidentNum ==> Case ID
• DBFHeaderID ==> Sub case ID
• DBFItemDescription ==> Type of Labors
• DBFQuantity_min ==> amount of minutes in SUBCASE per type.
• CALLTotalInvestedTimeMins ==> Total all the minutes in the Case

DBFIncidentNum	DBFHeaderID	DBFItemDescription	DBFQuantity_min	CALLTotalInvestedTimeMins
4750748597	4750748597-471	Hardware Repair/Installation	90	1251
4750748597	4750748597-471	Travel	60	1251
4750748597	4750748597-471	Travel	60	1251
4750748597	4750748597-472	Travel	60	1251
4750748597	4750748597-472	Travel	60	1251
4750748597	4750748597-000	Hardware Repair/Installation	0	1251

I have to sum the min for each Subcase.

this is easy just sum minute from DBFQuantity_min

BUT

The problem is that some time there is minute, like in this example, that are added into the case ONLY  and not the sub_case.

like you can see in case 4750748597-000

to know that I need to

check difference between 'CALLTotalInvestedTimeMins' field  that indicate total minutes in the case and sum of all 'DBFQuantity_min' field.

If there is a difference that mean that min were added into the case and not in the subcase

in this case I should get this table:

DBFHeaderID	OnSite_Travel_hours
4750748597-471	150
4750748597-472	120
4750748597-000	921

[crusader_ · Accepted solution]

Hi,

Please check this qvw.

Regards,

Andrei !

[#3]

Hello

Thank you

I learned 2 new functions thanks to you.

Can you explain me please why we need

sum(CALLTotalInvestedTimeMins) - max(aggr(sum(TOTAL DBFQuantity_min),DBFHeaderID))

and not only

sum(CALLTotalInvestedTimeMins) - ((sum(TOTAL DBFQuantity_min),DBFHeaderID))

without the Max and aggr?

I mean according to what I understand

I will have

sum(CALLTotalInvestedTimeMins) - ((sum(TOTAL DBFQuantity_min),DBFHeaderID))

                   ||                                -                            ||

                1251                             -                            210

[crusader_]

Hm... It's a bit more complicated than you think:)

((sum(TOTAL DBFQuantity_min),DBFHeaderID)) - gives you an error

aggr() function helps me to create a virtual table which contain 2 columns:

DBFHeaderID and sum(TOTAL DBFQuantity_min)

To operate with values in this table I need to apply another aggregation function (or rank, depends on my goals) - so that's why I use max()... In fact it could be a min()/avg() or whatever you want.... because we made sum(TOTAL DBFQuality_min) and for every single row we have one single value...

To learn more read this DOC QlikView Technical Brief - AGGR.docx

Hope this helps you.

Regards,

Andrei

[#5]

THANK YOU FOR YOUR PROFESSIONAL ANSWER

Have a nice evening