Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

Total issue

Hi Experts,

Can any one please help me on below issue.

In the below table I have calculated CC1 measure using below expression.

RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

CC1 total is 167,947,700 but getting Auto total as 120,672,758

The requirement is  I have to create the second table in the app with Name and Scope as dimension and CC1 as Measure but getting all wrong totals.

.

Please help me on this.

Thanks in advance.

1 Solution

Accepted Solutions

Re: Total issue

Hi Mahitha, in second table you will need an aggr that does the operation by date and sums all values:

Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1, Date, Name, SCOPE))


In the first table you can choose 'Sum' instead of 'Automatic' for the Totals function (CC1 column properties, almost at the end)

4 Replies
mahitham
Contributor

Re: Total issue

Hi,

Could you please help me on above issue.

stalwar1

jyothish8807

youssefbelloum

marinadorcassio1
Contributor

Re: Total issue

Hi Mahitha,

The total CC1 gives you bad result because the total isn't the sum the different values found for CC1, but is recalculated on the total data.

It does :

RangeMax(0,Sum(Value)) +


Sum(Value) here =

48906566

RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

Sum(Item1) here =

16410074

Sum(Item2) here =

28795310

etc...

Then does RangeMax on Sum(Item1), Sum(Item2)... /5 + 0,1, here =

71766192,1

Then sum 48906566 and 71766192,1, which =

120672758

You should try aggregating by the choosen dimensions.

Hope it helps,

Marina

ChennaiahNallani
Contributor III

Re: Total issue

replace null and missing values with Zero '0" and Try

Re: Total issue

Hi Mahitha, in second table you will need an aggr that does the operation by date and sums all values:

Sum(Aggr(RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1, Date, Name, SCOPE))


In the first table you can choose 'Sum' instead of 'Automatic' for the Totals function (CC1 column properties, almost at the end)

Community Browser