Total in Pivot

[Samanehsorournejad]

Hi every one,

I try to create the Dashboard with 2 Dimension and one Measure like this:

Dims:

Status_Name

STORY_POINTS is the same Points

Measure : avg(sum_step)

and I want to Calculate the sum of the values of Status_Name for each points

I had written this Measure but it shows me any thing:

=aggr(RangeSum({<STATUS_NAME={'Neu'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'In Bearbeitung'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'In Abnahme'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'Fertig Bearbeitung'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'In Qualitätssicherung'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'Freigabe'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'In Produktion'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'Fertig Qualitätssicherung'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'Bewertung'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'Rückmeldung erforderlich'}>}Avg(sums)),STORY_POINTS)
+
aggr(RangeSum({<STATUS_NAME={'Zurückgestellt'}>}Avg(sums)),STORY_POINTS)

 also I had turn on Total in Qlik sense but it does not shows me the right Values 😞

please help me!

[Or · Accepted solution]

I'm not quite sure what your current measure is meant to be doing, but you should be looking at something like:

Sum(Aggr(Avg(sums),STATUS_NAME)))

Which would calculate the average for individual STATUS_NAMEs and then sum the aggregate of those averages.

[Samanehsorournejad]

actually I had try your idea but it exactly show me the same value as avg(sums)😔

[Or]

That is what it should be showing, except in the Total column. There, it should show the sum of totals.

Load * INLINE [
Status, Value
A, 1
A, 2
B, 3
B, 4
C, 5
C, 6
];

If this isn't what you mean, could you perhaps clarify what you are after?

[Samanehsorournejad]

actually your solution is for sum of rows but I need to sum of my columns :
for example in my Pivot it should :
29,93+9,37+4,55+15,07+2,94 ................

[Or]

If I understand this correctly, the same formula should apply but use Points as the Aggr dimension instead of STATUS_NAME, or you may need to use both in your aggr() depending on how you expect the grand total to behave.

[Samanehsorournejad]

yes I had tried this :

sum(AGGR(Avg(sums), STORY_POINTS )) but it answer me just 0! I don't know why?:(

[Samanehsorournejad]

Hi again,

also I had change my Measure such as :

sum(AGGR(Avg({<STATUS_NAME={'Neu'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'Bewertung'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'Zurückgestellt'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'In Bearbeitung'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'Rückmeldung erforderlich'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'Fertig Bearbeitung'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'In Qualitätssicherung'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'Fertig Qualitätssicherung'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'In Abnahme'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'Freigabe'}>}sums), STORY_POINTS))+
sum(AGGR(Avg({<STATUS_NAME={'In Produktion'}>}sums), STORY_POINTS))

now it answer but I dont know why for any Point in one STATUS_NAME ??!

had any one any Idea?