Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Display values on a month_dimension (instead of day)

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

24 Replies
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

datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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.

marcus_sommer

If you want this kind of average you need to calculate on daily level. Your approach with divide the result by 5 could work, but if should only required a quota it isn't neccessary - try it out:

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

- Marcus

datanibbler
Champion
Champion
Author

Hi Marcus,

thanks! That looks cool. I'll try it. Just curious - what does this syntax with the '&' do? Is that part of the aggr() fct. or rather part of the COUNT?

Thanks a lot!

Best regards,

DataNibbler

P.S.: It seems to work. I will recalculate the individual parts to check if that is plausible - it seems so - but I think that is solved.

I'll be back. Once that is done, it's up to the next step...

marcus_sommer

It counted both fields - I have Weekday simply as native field in your data-model assumed, if not you need it - in a concatenate. This meant for example: 8 people * 5 weekdays = 40 person days.

- Marcus

datanibbler
Champion
Champion
Author


Hi Marcus,

I just checked the COUNT of the emps who are out sick with your formula - but that again returns a value of 3 for the past week, when it should be around 2.6 - there were 2 out sick on Mon and Tue, 3 from Wed to Fri.

That is not much of a difference - but it might be more in other cases.

Can you think of a reason for this?

Thanks a lot!

Best regards,

DataNibbler

Ah, I had not realized that - ok, so I'll have to change the name. I do have a native field of the kind. Let's see.

P.S.: No - that returns a value of 15 - 5 days * 3 persons - still close, but not quite it...

datanibbler
Champion
Champion
Author

Hi Marcus,

okay, let's leave it at that - your formula works correct all right - there were 5 distinct emps out sick last week - but I have to consider that there were 2 sick on Mon etc...

I can do it the other way all right. Five days is not too much.

If you don't mind, I'll be back lateron with the next step unless I can think of a way myself - I have to calculate an average YTD_value.

Best regards,

DataNibbler

marcus_sommer

This should return 2.6

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


- Marcus

datanibbler
Champion
Champion
Author

Hi Marcus,

that works!

What is the difference to the COÚNT in the first formula you proposed? I don't see it.

I just realized that my method would do for the past week - but not for the past month, since I don't want to generate 31 variables and calculate 31 individual quotas...

Perfect! I'll try it.

P.S.: Strange - with that >division by 5<, it works fine for the sick emps (where that Kennz_krank is there), but not for the total nr. - that has been the same on all days of last week, so it should be the same on average - but it is higher. I have to replace that with a >division by 7<, then it is correct.

What causes that difference?