Display values on a month_dimension (instead of day)

[datanibbler]

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

[tresesco]

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

[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

[tresesco]

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.

[datanibbler]

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.

[datanibbler]

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

[tresesco]

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

[datanibbler]

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 😉

[datanibbler]

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

[datanibbler]

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