Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Total Sum in Tables - total sum ignoring the formula

Hey everyone,

we need your help please:

In the above table we are calculating some figures per branch (e.g. Actual Malus Delivery Quota). The problem is that the total line is always using the the formula as all other lines within the table. BUT we need to have a total sum (more or less ignoring the above mentioned formula). In this case our desired result would be 2.900 € instead of 900 €.

A possible solution could also be that the figure is displayed in a different object (e.g. KPI, Second table etc.).

But here comes our second problem:

Our formula (see formula at the bottom) is based on Branch level therefore the it is a bit difficult to get our desired result on country level.

If you need any additional information to help us - please contact us!!! We really have to solve this problem and would really appreciate if you could help us!

Many, many thanks in advance!

Best regards

Lukas & Whiteside

🙂

if((if((round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

/

(count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150)>0,(round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

/

(count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150),0)

)>(if((count([Shipment no])< 51), 100 , if((count([Shipment no]) < 101), 500 , if((count([Shipment no]) < 1501),1000 ,1500)))),(if((count([Shipment no])< 51), 100 , if((count([Shipment no]) < 101), 500 , if((count([Shipment no]) < 1501),1000 ,1500)))),(if((round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

/

(count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150)>0,(round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

/

(count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150),0)

))

2 Replies
sunny_talwar

Wrap your expression around with Sum(Aggr()) like this

Sum(Aggr(

if((if((round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

/

(count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150)>0,(round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

/

(count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150),0)

)>(if((count([Shipment no])< 51), 100 , if((count([Shipment no]) < 101), 500 , if((count([Shipment no]) < 1501),1000 ,1500)))),(if((count([Shipment no])< 51), 100 , if((count([Shipment no]) < 101), 500 , if((count([Shipment no]) < 1501),1000 ,1500)))),(if((round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

/

(count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150)>0,(round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

/

(count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150),0)

))

, [Branch country], [Branch city]))

Anonymous
Not applicable
Author

Many thanks! That works! 🙂