Announcements
cancel
Showing results for
Did you mean:
Creator

## How to get the total of a measure and show it in all rows of a table

I have an easy measure that does the following

``````// All Other Employees Measure
Sum(
Aggr(
{< Employee= >}  // Ignore selection of Employee
Count({< ACTION_CODE = {'LABOR_OFF'} >} distinct MainSFC) * Avg_time,
MainSFC, Employee
)
)
/
(
Sum(
Aggr(
{< Employee= >}  // Ignore selection of Employee
Count(distinct WorkingDay) * 7.5 * 60,
Employee
)
)
-
Sum(Absence) * 60

)``````

I then add another measure, which is the same but with only TOTAL suffix added to the two SUM functions.

This results in a table with the following values

But as you can see, while the values on the left are correct, the results on the right are not what I was looking for. The right one should be all 75.4% for each single row, because it's the total value at the top of the first column to the left.

Let's make an example:

 Measure 1 Total measure average: 50 average: 50 25 50 75 50

So the final result should be 50, because the 2 rows gave me a total average of 50, and all rows in the second measure should thus have 50.

Labels (1)

1 Solution

Accepted Solutions

do you want below output

if yes, then use below expression

Sum(Total
Aggr(
{< Employee= >}  // Ignore selection of Employee
Count({< ACTION_CODE = {'LABOR_OFF'} >} distinct MainSFC) * Avg_time,
MainSFC, Employee
)
)
/
(
Sum(Total
Aggr(
{< Employee= >}  // Ignore selection of Employee
Count(distinct WorkingDay) * 7.5 * 60,
Employee
)
)
-
Sum(Total Absence) * 60

)

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
10 Replies

Since we don't have idea about your data, it is difficult to say which one is correct & which one is wrong. according to you which one is correct? what are you trying to achieve? Can you explain with sample data?

Also, where do you need our help? in understanding the expression logic or need help in correct expression?

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Creator
Author

Hello @PrashantSangle , thank you for the answer.

I tried to add more context to the question, let me know if you need more details, but the values to the left are simply the results of the measure, and I want to make a measure that will give me only the total avg (the top bold 75,4%) value at the top for all of the rows.

Master

Hi

May be try to add NO DISTINCT qualifier or TOTAL Qualifier

// All Other Employees Measure
Sum( NO DISTINCT
Aggr(
{< Employee= >}  // Ignore selection of Employee
Count({< ACTION_CODE = {'LABOR_OFF'} >} distinct MainSFC) * Avg_time,
MainSFC, Employee
)
)
/
(
Sum(
Aggr(
{< Employee= >}  // Ignore selection of Employee
Count(distinct WorkingDay) * 7.5 * 60,
Employee
)
)
-
Sum(Absence) * 60

)

@alespooletto , is it possible for you to share the app?

Actually it is simple by using TOTAL keyword in set expression, but as I can see your orginal expression is complex in itself only, so difficult to give you correct answer.

But in simple scenario, we use

Sum(TOTAL<dimension_name> sales)

If you used dimension name along with total then it will give you total sales at group of dimension level.

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Creator
Author

@PrashantSangle unfortunately it's not possible, because it contains private data. I will try to make a sample of data tomorrow, and give it back to you.

I tried with that simple approach, the Sum (total <fld> ) but it gives me a wrong number at the end of the total.

Tomorrow I will try to provide it.

Creator
Author

Hello @PrashantSangle

I tried to collect only a few of the data, you should be able to use the formula above to retrieve the exact situation.

I can see only 3 columns in attached excel. MainSFC, Dipendente and Tempo_Medio

In your expression there are few more fields. like Employee, ACTION_CODE, Avg_time, WorkingDay, Absence

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Creator
Author

Hi @PrashantSangle I must have attached the wrong file, you can find a table containing all the information here

do you want below output

if yes, then use below expression

Sum(Total
Aggr(
{< Employee= >}  // Ignore selection of Employee
Count({< ACTION_CODE = {'LABOR_OFF'} >} distinct MainSFC) * Avg_time,
MainSFC, Employee
)
)
/
(
Sum(Total
Aggr(
{< Employee= >}  // Ignore selection of Employee
Count(distinct WorkingDay) * 7.5 * 60,
Employee
)
)
-
Sum(Total Absence) * 60

)

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂