Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
realpixel
Creator
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
sunny_talwar

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

View solution in original post

5 Replies
MK_QSL
MVP
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))

realpixel
Creator
Creator
Author

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

  

LIVRAISONTotal HMCONTTotal EVP
-38905517
3890

5517

  

Result for year

  

LIVRAISONTotal HMCONTTotal EVP
DEPO2594738137
LIPN40225692
-1606523285
4603467114

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


  

LIVRAISONTotal HMCONTTotal EVP
DEPO288377
LIPN27273887
LIVT8751253
38905517

It seem to be fine by month.

But not sum for the year. An idea?

  

LIVRAISONTotal HMCONTTotal EVP
DEPO24793248
LIPN3172246841
LIVT1100215641
4520365730
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

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
Creator
Creator
Author

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


Regards