Problem sum/count chart

[realpixel]

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

[MK_QSL]

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))

[realpixel]

thank for your reply.

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

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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))

[realpixel]

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

Regards