Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL:
**Learn More**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Problem sum/count chart

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

realpixel

Creator

2018-03-23
05:33 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

MVP

2018-03-23
07:59 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

819 Views

5 Replies

MK_QSL

MVP

2018-03-23
05:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2018-03-23
07:45 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 |

819 Views

sunny_talwar

MVP

2018-03-23
07:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

sunny_talwar

MVP

2018-03-23
07:59 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

820 Views

realpixel

Creator

2018-03-23
09:53 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Regards

819 Views