Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Creator

## Problem sum/count chart

Hello,

In my board I have dimension « LIVRAISON » and I want to count/Sum of field « HMCONT » and also « EVP » field which can have a value « 1 » or « 2 » , by month or by year

In my chart I use the following expression.

Total HMCONT = count(distinct if( EVP='1', HMCONT)) + count(distinct if( EVP='2', HMCONT))

Total EVP = count(distinct if( EVP='1', HMCONT)) + (count(distinct if( EVP='2', HMCONT))*2)

I use distinct function because HMCONT field can have a duplicate value.

If I do a selection from calendar month by by month, the sum (Total HMCOUNT & Totla EVP) is correct but when I use selection calendar only by year on 2017 for example, the total of year is not correct.

Example, if I take value month by month from QV and export to Excel the sum is 46 033 for Total HMCOUNT and Total EVP is 67 113 but in QV for year 2017 I have Total HMCOUNT = 44 848 and Total EVP = 65 215. Why this difference ? Maybe expression is not correct ?

 Month Total HMCOUNT Total EVP 01 3890 5517 02 3544 5209 03 3414 4864 04 3247 4738 05 3669 5333 06 4022 5692 07 3784 5462 08 3985 5887 09 3838 5764 10 4416 6455 11 4089 5979 12 4135 6213 Total 46033 67113

Regards

1 Solution

Accepted Solutions
MVP

I think you need to add LIVRAISON as one of the dimensions in the aggr() function

Sum(Aggr(Count({<EVP = {'1','2'}>} DISTINCT HMCONT), Mois, LIVRAISON))

and

Sum(Aggr(Count({<EVP = {'1'}>} DISTINCT HMCONT) + Count({<EVP = {'2'}>} DINSTINCT HMCONT)*2, Mois, LIVRAISON))

5 Replies
MVP

Try this

SUM(Aggr(Count({<EVP = {'1','2'}>}Distinct HMCONT),Month))

SUM(Aggr(Count({<EVP = {'1'}>}Distinct HMCONT) + Count({<EVP = {'2'}>}Distinct HMCONT)*2,Month))

Creator
Author

Result for month 01

Expression

SUM(Aggr(Count({<EVP = {'1','2'}>}Distinct HMCONT),Mois))

and

SUM(Aggr(Count({<EVP = {'1'}>}Distinct HMCONT) + Count({<EVP = {'2'}>}Distinct HMCONT)*2,Mois))

 LIVRAISON Total HMCONT Total EVP - 3890 5517 3890 5517

Result for year

 LIVRAISON Total HMCONT Total EVP DEPO 25947 38137 LIPN 4022 5692 - 16065 23285 46034 67114

The detail of LIVRAISON is not correct

Now if I modify expression like that.

SUM(Aggr(Count({<EVP = {'1','2'}>}Distinct HMCONT),LIVRAISON))

and

SUM(Aggr(Count({<EVP = {'1'}>}Distinct HMCONT) + Count({<EVP = {'2'}>}Distinct HMCONT)*2,LIVRAISON))

Result for month 01

 LIVRAISON Total HMCONT Total EVP DEPO 288 377 LIPN 2727 3887 LIVT 875 1253 3890 5517

It seem to be fine by month.

But not sum for the year. An idea?

 LIVRAISON Total HMCONT Total EVP DEPO 2479 3248 LIPN 31722 46841 LIVT 11002 15641 45203 65730
MVP

I believe mrkachhiaimp‌'s expression should resolve your issue... but to explain why there is a difference is because you are doing a DISTINCT count of HMCONT which means that it will be counted only once per dimension. Now it may be a case that a certain HMCONT repeats in 2 or more months... for the month dimension... it will counted 1 time each month... but total will also count it just one even though it was in 2 or more months.

For example

HMCONT = abc was in month 2, 4, and 6

Month     Concat(HMCONT, ',')     Count(DISTINCT)

1

2                    abc                                   1

3

4                    abc                                    1

5

6                    abc                                     1

7

8

9

10

11

12

TOTAL          abc, abc, abc                       1

Althought abc repeated 3 times, the distinct count of abc is still 1 for the total.

In order to get a count of 3... you can use Sum(Aggr())... which will sum the individual rows rather than performing the row level calculation at the total also. Open the link below to read more about this

sum of rows in pivot tables

MVP

I think you need to add LIVRAISON as one of the dimensions in the aggr() function

Sum(Aggr(Count({<EVP = {'1','2'}>} DISTINCT HMCONT), Mois, LIVRAISON))

and

Sum(Aggr(Count({<EVP = {'1'}>} DISTINCT HMCONT) + Count({<EVP = {'2'}>} DINSTINCT HMCONT)*2, Mois, LIVRAISON))

Creator
Author

Thank you very much for your help, it works fine now

Regards

Community Browser