topic Set analysis in QlikView

Set analysis

Thu, 09 Aug 2012 19:07:42 GMT

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

Re: Set analysis

swuehl — Thu, 09 Aug 2012 21:54:53 GMT

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

Re: Set analysis

Fri, 10 Aug 2012 13:10:31 GMT

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

Re: Set analysis

swuehl — Fri, 10 Aug 2012 13:37:57 GMT

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

Re: Set analysis

Fri, 10 Aug 2012 14:01:45 GMT

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