Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I'm having trouble with set analysis. I work on a graph whose dimensionality is the number of days of the month. Each of these numbers is associated to a weekday contained in the column NoJourSemaineAchalandage . Here is the expression I would like to write:
Count(DISTINCT {$<SousTypeFacture=SousTypeFacture-{"Pilulier"},NoJourSemaine=NoJourSemaineAchalandage>} Total NoPrescription)/ (COUNT(DISTINCT {$} Total NoSite))/Count(distinct {$<NoJourSemaine=NoJourSemaineAchalandage>} Total NomFraJourSemaine)
However the part NoJourSemaine = NoJourSemaineAchalandage does not work.
Please find attached an example of qvw where there is my model and the graph with the mistake.
Do you know a way to do this?
Thank you
Melissa
Melissa,
I'm sorry, I haven't really got what you're after, but maybe something like this (i.e. using the p() function for the NoJourSemaine field modifier) ?
=Count(DISTINCT {$<NoJourSemaine=p(NoJourSemaineAchalendage)>} Total NoPrescription)/
(COUNT(DISTINCT {$} Total NoSite))/Count(distinct {$<NoJourSemaine=p(NoJourSemaineAchalendage)>}
Total NomFraJourSemaine)
Regards,
Stefan
Stefan,
Thank you for your answer.
I ever tried this solution but with the function P() the column NoJourSemaineAchalendage is independant of the dimentionality and it's not that I want to. Indeed, the NoJourSemaineAchalendage has to change whith the NoJourMois (Dimensionality).
Regards,
Mélissa
Ah, I see.
No good news, then: the set expression is evaluated once per chart, not per dimension value.
Try using a conditional instead, like
Count(TOTAL DISTINCT if(NoJourSemaine=NoJourSemaineAchalendage, NoPrescription))
Another issue could be the use of total in all aggregation functions in your expression, this will not take the dimension into account for any part of the expression.
So all in all, maybe something like this?
=Count( DISTINCT if(NoJourSemaine=NoJourSemaineAchalendage,NoPrescription))
/ (COUNT(DISTINCT Total NoSite))/Count(total distinct if(NoJourSemaine=NoJourSemaineAchalendage, NomFraJourSemaine))
[I am sorry, still no clue what you want to show here]
Regards,
Stefan
Thank you again for your answer.
With this solution :
Count(TOTAL DISTINCT if(NoJourSemaine=NoJourSemaineAchalendage, NoPrescription))
NoJourSemaineAchalendage is independant of the dimensionality because of "Total"
and with this solution :
Count( DISTINCT if(NoJourSemaine=NoJourSemaineAchalendage,NoPrescription))
NoPrescription is dependant of the dimensionality.
I would like a way to have NoJourSemaineAchalendage dependant of the dimensionality and NoPrescription independant of the dimensionality.
Regards,
Mélissa