New measure based on existing measure (Aggregation function based on existing aggregation )

[Mini_Elaine]

Hi everyone,

I am trying to create a new measure based on another measure. My dataset looks like below:

Project	Status	Total sale	Sale date
A	Open	1	01/05/2024
B	Closed	1	02/04/2025
B	Closed	1	01/05/2019
B	Closed	1	04/10/2018
C	Feasibility	0	-
D	Closed	0	-
E	Closed	1	01/02/2019
E	Closed	1	05/06/2020
E	Closed	1	01/02/2018
E	Closed	1	27/07/2021
F	Open	1	12/12/2023
F	Open	1	02/08/2022
F	Open	1	30/05/2016
F	Open	1	28/09/2022
G	Feasibility	0	-
H	Closed	0	-
I	Closed	1	05/02/2025
I	Closed	1	07/10/2024
I	Closed	1	29/10/2022
I	Closed	1	04/05/2022
I	Closed	1	25/10/2017
I	Closed	1	15/06/2018

I would like to measure % of Closed project without any sale/All closed project.

I noticed I have to use total sale for each project as a condition to calculate the 'Closed project without any sale'. The Qliksense returns an error to me saying 'you can not use aggregation within aggregation'. 

How can I get this measure?

Many thanks!

[henrikalmen]

To get the percentage of projects that are closed without any sales you just count them, and divide by the total number of unique projects. I'm assuming that you don't have any projects in your data model that have both 0 and higher numbers as Total sale. 

Try using set expressions, like this: count({<Status={'Closed'}, [Total sale]={0}>}distinct Project) / count({<Status={'Closed'}>distinct Project)

What was your expression that gave you the error message? In the expression above I am also assuming that [Total sale] is a field in the data model, not a calculation in frontend.

[stevedark]

Hi @Mini_Elaine 

Can you please advise exactly what figures you want to come out of that table.

Is it closed without sale being 2 rows, divided by all rows (22), giving 9%?

If so, you will be wanting something like:

sum(aggr(if(sum(Sale) = 0 and Status = 'Closed', 1, 0), Project, Status, [Sale date])) / 
sum(aggr(1, Project, Status, [Sale date]))

The aggr function is essential if ever you want to have an expression where you have one aggregation function inside another (as in the first line of that expression).

It creates a virtual table with the dimensions you give as parameters (in this case Project, Status and Sale date) and you can calculate a new column for each of those combinations of values. You are then able to do a further calculation over the contents of that new column.

The first part of the expression giving 2 (given your example data) and the second giving the count of all combinations of those values (as 1 will appear in the new column for all combinations).

I hope that makes sense?

Steve

[Mini_Elaine]

Thank you Steve. I just tried the coding but didn't work.....

I would like to calculate:

Total sale based on each project: Sum[total sale]

Total project: Count(distinct [Project])

Total closed project: Count({<[Status]={'Closed'}>}distinct [Project])

Total closed without sale project: I couldn't get this formula right because I have to use Sum[total sale]

And then I would like to know: % Total closed without sale project/Total closed project

Thank you in advance!

[henrikalmen]

Ok, so I thought [Total sale] was a field in the data model, counting the number of sales so that it was a flag 0 or 1 per date.

The suggestion from stevedark takes into account the sale date, but since there can be more than one sale date per project you should probably use his formula but remove sale date from it. Or maybe not just that, perhaps this:

sum(aggr(if(sum(Sale) = 0 and Status = 'Closed', 1, 0), Project)) / 
sum(aggr(if(Status = 'Closed', 1, 0), Project))

[stevedark · Accepted solution]

Hi @Mini_Elaine 

So, you are looking for 2 projects closed without sale, divided by 9 projects, so 22%?

If so it will be more like:

sum(aggr(if(sum([total sale]) = 0 and minstring(Status) = 'Closed', 1, 0), Project)) / 
sum(aggr(1, Project))

It's not clear whether your value field is [Total sale] or [total sale] or whether that is the result of an expression and the field is something different again. Whatever the field is that needs to go into the expression in the right place.

sum(aggr(1, Project)) and count(DISTINCT Project) will give exactly the same number.

Hope that moves you towards a solution.

Steve

[Mini_Elaine]

Thank you both @stevedark and @henrikalmen ! Finally I got solutions and both ways work perfect!