Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

heij1421
New Contributor III

Help with Formula to calculate total with conditions

Attached you find this dashboard

In the last graph I need an additional column. The calculation should be the sum of the red box in case of the first line.

This is the total amount of 'Team Cor' except where 'team cor' (linked to cost center of a project) <> team cor (linked to cost center of an employee).

How do I do that?

5 Replies
MVP
MVP

Re: Help with Formula to calculate total with conditions

I suggest that you reduce the data in your upload. I will not open 65MB file to see if can help you.

Preparing examples for Upload - Reduction and Data Scrambling

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
heij1421
New Contributor III

Re: Help with Formula to calculate total with conditions

Jonathan, I already reduced the data using the 'reduce data - keep possible values' option.

MVP
MVP

Re: Help with Formula to calculate total with conditions

OK, but I suggest that you reduce it still further...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
heij1421
New Contributor III

Re: Help with Formula to calculate total with conditions

I figured it out myself.

Solution:

=SUM(AGGR(IF([Projects CostCenterDescription]=[Employee CostCenterLabel],SUM(Total <[Employee CostCenterLabel]>[Projectactuals RealQuantityCosts] * [Item Sales Package Price])-SUM([Projectactuals RealQuantityCosts] * [Item Sales Package Price])),[Projects CostCenterDescription], [Employee CostCenterLabel]))

heij1421
New Contributor III

Re: Help with Formula to calculate total with conditions

I figured it out myself.

Solution:

=SUM(AGGR(IF([Projects CostCenterDescription]=[Employee CostCenterLabel],SUM(Total <[Employee CostCenterLabel]>[Projectactuals RealQuantityCosts] * [Item Sales Package Price])-SUM([Projectactuals RealQuantityCosts] * [Item Sales Package Price])),[Projects CostCenterDescription], [Employee CostCenterLabel]))

Community Browser