Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a problem with aggr fonction :
I need t show the % of dossiers by Doss_Fase per year in a pivot table.
I'm using this Expression : count(distinct Dossier_Id)/aggr(count(distinct Dossier_Id), Dos_CreaYear)
and getting this result :
Dos_CreaYear | Dos_FaseDescrFR | # Dossiers | % Dossiers |
---|---|---|---|
2015 | Amiable | 3495 | 85% |
Judiciare | 615 | ||
Total | 4110 | 100% | |
2014 | Amiable | 2172 | 80% |
Judiciare | 530 | ||
Total | 2702 | 100% | |
2013 | Amiable | 2518 | 79% |
Judiciare | 680 | ||
Total | 3198 | 100% | |
Total | 10010 |
Do somone know why it's not showing the Judiciaire ratio ?
Thanks for help
Try this
Count(DISTINCT Dossier_Id)/Count(DISTINCT TOTAL <Dos_CreaYear> Dossier_Id)
Try this
Count(DISTINCT Dossier_Id)/Count(DISTINCT TOTAL <Dos_CreaYear> Dossier_Id)
Because you have not included Dos_FaseDescrFR as a dimension in the Aggr(). But before you add it, I don't think you need an aggr() here. Something like
Count(distinct Dossier_Id) / Count(distinct total<Dos_CreaYear> Dossier_Id)
should work (correct any field names or syntax errors). If not I, suggest that you post a sample qvw file with enough sample data and the table excluding the % calculation.
Thanks it works ! Dan you explain what was wrong in my expression and what's the right syntaxe to use in this case ?
Thanks in advence !
Yes it works too...i didn't know that i can use distinct total <dimension>...now i know but i still need to control the syntaxe...
What you were seeing was one of the Pitfalls of the Aggr function. Specifically, its called grain mis-match... you can address the issue by using NODISTINCT
Count(DISTINCT Dossier_Id)/Aggr(NODISTINCT Count(DISTINCT Dossier_Id), Dos_CreaYear)
Which will ensure that you have value for your denominator for each row (Without NODISTINCT, the value is only populated on just one of the many recurrences of Dos_CreaYear.
We (Me and jontydkpi) proposed an alternative, which is much better than using Aggr() to find the total of your count for each Dos_CreaYear using TOTAL function.