4 Replies Latest reply: Aug 10, 2012 10:01 AM by melissa.bardonnaut RSS

    Set analysis

      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
          Stefan Wühl

          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

              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
                  Stefan Wühl

                  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

                      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