25 Replies Latest reply: Sep 17, 2013 9:54 AM by Friedrich Hofmann RSS

    Display values on a month_dimension (instead of day)

    Friedrich Hofmann

      Hi,

       

      in most of my diagrams, I have two optional dimensions: Day (in which dase six data_points for Mon-Sat are displayed) and month (in which case twelve data_points are displayed).

      For several diagrams so far I have just used the avg() fct. around the existing expression to show the values per month rather than per day - I have different expressions dependent on a variable which can be switched via a button).

       

      <=> In my newest diagram, I have data from a database and I have a COUNT(DISTINCT ) as an expression.

      => When I try placing that inside an avg() fct like I have done so far, the editor says "Error in expression".

      Can anybody tell me what's wrong with that and, more importantly, what I could do instead to the same effect?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Display values on a month_dimension (instead of day)
          Tresesco B

          Avg(Count(Distinct)) does not look very convincing logic for me. You won't get an effective average when you count it distinct, right?

          • Re: Display values on a month_dimension (instead of day)
            Marcus Sommer

            Aggregations couldn't be nested unless with a additional total-statement - nevertheless most often it don't work. For such you could use aggr() which build in background a virtual table on which the actual aggregation will calculated. Search here in blog-area for blogs from Henric Cronström about aggr() and which averages is the real average.

             

            - Marcus

              • Re: Re: Display values on a month_dimension (instead of day)
                Tresesco B

                To add to marcus_sommer here from help:

                 

                As a general rule, it is not allowed to
                nest aggregations in a QlikView chart expression. From version 7.5 there is
                however one very important exception to this rule. As long as you use the total qualifier in the inner aggregation function, the nesting
                is allowed.

                 

                Say for example that you want to
                calculate the sum of the field Sales, but only include transactions with an
                OrderDate equal to the last year. The last year can be obtained via the
                aggregation function max(total year(OrderDate)).

                 

                An aggregation as follows would then do
                the job:

                 

                sum( if(year(OrderDate)=max(total year(OrderDate)), Sales)).

                 

                The inclusion of the total qualifier is absolutely necessary for this kind of
                nesting to be accepted by QlikView, but then again also necessary for the
                desired comparison. This type of nesting need is quite common and should be used
                wherever suitable.

                  • Re: Display values on a month_dimension (instead of day)
                    Friedrich Hofmann

                    Hi tresesco,

                     

                    I've had a look in "QlikView 11 for developers" for the TOTAL parameter - but that says that parameter is used to disregard dimensions and instead perform a calculation on the entire dataset?

                    That is not exactly what I want to do since I want to generate an average of only one entire month and display that when the dimension >month< is selected - so the date is relevant.

                    I also need my COUNT() - I can't think of any other way right now. I can do without the DISTINCT qualifier.

                    I will try to come up with something using the aggr() fct.

                     

                    Thanks a lot anyway!

                    Best regards,

                     

                    DataNibbler

                     

                    P.S.:

                    @ Marcus

                    I have read Henric Cronström's blog about averages - I cannot say I understood it - but I guess that a regular avg is what is used by most people, so it should be good enough for this purpose. Anything more sophisticated would probably lead me to explaining in regular intervals how my figures are calculated and why...

                     

                    P.P.S.:

                    The aggr() fct seems to work fine.

                      • Re: Display values on a month_dimension (instead of day)
                        Friedrich Hofmann


                        Hi,

                         

                        it seems there are some problems with this - the figures are wrong:

                        - With my normal COUNT (dimension is >day<, I get an employee nr. of a bit over 600)

                        <=> When I change a variable, the dimension changes to >month<:

                           => My formula currently looks like this

                         

                        aggr(COUNT( Pers_Nr5), Monat)

                        but that returns a value over 85.000...

                         

                        Can someone help me here, please? It's obvious I've misunderstood something in the Help file, but I don't see what...

                         

                        Thanks a lot!

                        Best regards,

                         

                        DataNibbler

                          • Re: Display values on a month_dimension (instead of day)
                            Tresesco B


                            Without knowing the data, it would be very hard to answer. Please share a sample file with small data.

                              • Re: Display values on a month_dimension (instead of day)
                                Friedrich Hofmann

                                There are strange things happening here - or maybe not?

                                We have changed tack on this behalf today because we decided one way would be too complicated, so we chose another - and now I've noticed I do need the DISTINCT identifier. When I use that, it seems fine. The results of the aggr() function are just like they should be.

                                I just write in my formula

                                >>>   aggr(COUNT(DISTINCT Pers_Nr5), Monat) <<<

                                and it works.

                                 

                                I'm happy for the moment - let's see what happens next ;-)

                                  • Re: Display values on a month_dimension (instead of day)
                                    Friedrich Hofmann

                                    Hi,

                                     

                                    now I have one more challenge:

                                    - I have a status_light for this diagram, as for many others.

                                    - That light is calculated rgd. the avg value of a KPI in the past week
                                       => if the avg. quota in the past week is >96%, the light is supposed to be green, otherwise red.
                                      <=> It seems that for the same reasons that prevented me from using an avg around my COUNT in the first place I cannot use a set_expression in that aggr() function to calculate the value of the past week.

                                     

                                    Can you tell me how I could build this into the formula?

                                     

                                    Thanks a lot!

                                    Best regards,

                                     

                                    DataNibbler

                                      • Re: Display values on a month_dimension (instead of day)
                                        Friedrich Hofmann

                                        Hi,

                                         

                                        I'm working on this myself - but for some reason the aggr() fct. does not work anymore when I try to combine it with a set_expression (to display the aggregated value for the past week)

                                         

                                        Can anyone help me with this, please?

                                        Thanks a lot!

                                        Best regards,

                                         

                                        DataNibbler

                                          • Re: Display values on a month_dimension (instead of day)
                                            Marcus Sommer

                                            Set analysis and aggr() could be combined - I assume there is a syntax-error in the set analysis. Try to run the expression-parts alone and if it worked set the parts together again.

                                             

                                            - Marcus

                                              • Re: Display values on a month_dimension (instead of day)
                                                Friedrich Hofmann

                                                Hi Marcus,

                                                 

                                                I actually thought so, just couldn't figure it out yet. Maybe if I wasn't working on four different apps parallely... I will try. I have to calculate a quota and compare that to a fixed target_value, so I need several COUNTs.

                                                After that I will have one more step to take - calculating the "unlimited" YTD average - but let's take one step at a time.

                                                I'll be back to tell you how it's going.

                                                 

                                                Thanks a lot!

                                                Best regards,

                                                 

                                                DataNibbler

                                                  • Re: Display values on a month_dimension (instead of day)
                                                    Friedrich Hofmann

                                                    Hi Marcus,

                                                     

                                                    as usual: I'm one step further, but not there yet.

                                                    It does work, but the result is not what I wanted: My code currently is

                                                     

                                                    aggr(((COUNT({1<Woche = {$(=(Week(TODAY())-1))}>}  DISTINCT Pers_Nr5) - COUNT({1<Woche = {$(=(Week(TODAY())-1))}, Kennz_krank = {'X'}>}  DISTINCT Pers_Nr5)) / COUNT({1<Woche = {$(=(Week(TODAY())-1))}>}  DISTINCT Pers_Nr5)), Woche)

                                                     

                                                    ( I count (distinct) emp_nrs, subtract the count of employees sick and divide the result by the total nr. of emps (same as the first)).

                                                    The problem with that is:

                                                    - On Mon and Tue of last week, 2 empf were out sick, from Wed to Fri 3. Of those 3, 2 were apparently the same, one new.

                                                    => The COUNT (even nested within the aggr() fct.) returns 3 distinct, but it should return an average which is around 2.6.

                                                     

                                                    Can you help me there?

                                                    I guess one way would be to calculate the whole thing for every Weekday of the last week and divide it by 5, but that would be quite some typework and maybe not the most elegant way.

                                                     

                                                    Thanks a lot!

                                                    Best regards,

                                                     

                                                    DataNibbler

                                                     

                                                    P.S.: Since I cannot think of a better way just now, I'll get started on the longer route - putting the exact date of every day of the past week in a set_expression and then dividing the result by 5. This won't do for my YTD_thing, which I need for the "team homepage" for management, but for this step it will do.