topic Re: Double aggregation in QlikView

Double aggregation

datanibbler — Thu, 27 Jul 2017 10:21:08 GMT

Hi,

I have a slider_object to control the Display of values in my Chart:

The Chart has several time-related dimensions (month, calweek, date, hour) and by using the slider, the user can choose to look at only the time_intervals where the Expression in the Chart is over a certain treshold.

The slider_object has a variable max_value: The max value is, dependent on the currently active Dimension (month, cw, day or hour), the max value there is to Display. The steps are automatic, always starting at 0.

Just to check the results, I would like to know now which is the Dimension_point (time_interval) where that max_value was found - how can I do that?

My formula for the max_value is >> max(aggr(sum(Counterfield), Month)) << -> This works and Returns me the max value of the Expression within the Limits of my Dimension "Month" - now I'd like to know the month where the formula found this max_value - how to do that? I'm at a loss here.

Can someone help me with that?

Thanks a lot!

Best regards,

DataNibbler

Re: Double aggregation

sunny_talwar — Thu, 27 Jul 2017 11:23:34 GMT

May be this

FirstSortedValue(DISTINCT Month, -Aggr(Sum(Counterfield), Month))

Re: Double aggregation

datanibbler — Thu, 27 Jul 2017 15:00:13 GMT

Yep!

Thanks Sunny! That works.

Re: Double aggregation

datanibbler — Fri, 28 Jul 2017 07:45:18 GMT

Hi Sunny,

sorry, I just don't understand just what this function does - I Need some real Training eventually. So what would I do if I'm looking not for the Maximum, but for the Minimum value?

Is there something like >> LastSortedValue <<?

Re: Double aggregation

antoniotiman — Fri, 28 Jul 2017 08:35:17 GMT

Hi Friedrich,

remove '-' from -Aggr(.......

Regards,

Antonio

Re: Double aggregation

sunny_talwar — Fri, 28 Jul 2017 11:05:14 GMT

Antonio is right, the expression I gave you initially was picking the firstSortedValue when the Sum(Counterfield) was sorted in descending order (negative sign before the Aggr() function)

FirstSortedValue(DISTINCT Month, -Aggr(Sum(Counterfield), Month))

In order to get the minimum, you just need to remove the negative sign, so that the sort is done in ascending order of Sum(Counterfield)

FirstSortedValue(DISTINCT Month, Aggr(Sum(Counterfield), Month))

Re: Double aggregation

datanibbler — Mon, 31 Jul 2017 10:45:02 GMT

Hi,

this works - or rather, I assume that it does - but I have the following issue now: Apparently, I have records in my underlying table where there is a username, but there is no session_timestamp, thus I cannot calculate a month - I used to filter those out in the script - or I tried - but apparently that did not quite work out:

The min(aggr()) construct Returns a min value of 0 - which I don't see in the Chart - and the other function does not return a month - which is probably because there is no month connected with These records - but this does not make sense: If a COUNT(DISTINCT(User)) Returns 0, then there is nothing to Count (or just no user_entry) - but there isn't a session_timestamp, either - so what is there in the data?

I will look into this question using a tablebox.

P.S.: I have no idea - just after that table has been loaded, both functions work correctly. Then Comes a series of aggregations - and after that, I get those queer results. So now I'll Experiment with an EXIT at different Points in the script ...

Re: Double aggregation

sunny_talwar — Mon, 31 Jul 2017 11:19:06 GMT

Would you be able to share a sample? I am not entirely sure I understand what issue you are having.

Re: Double aggregation

datanibbler — Mon, 31 Jul 2017 11:29:30 GMT

Never mind. I understand now.

It was because I have two tables which are actually separate, but I have linked them via the calendar. So now there are records in one of those tables with a timestamp which is not even in my calendar - my calendar as yet is not Independent of the tables - I have taken it via a BINARY from the GovernanceDashboard and I assume it ultimately depends on the SessionTaskAuditMaster. These records which cannot be linked to my calendar and to the STAM itself, fuck up the function ...

=> So I have two Options here - either modify that function to search for the second-lowest value and thus to ignore those records from the "Performance" table - or introduce a new calendar which can hold all records.

I guess I'll go for the second Option, I would have to eventually do that anyway.

Re: Double aggregation

datanibbler — Tue, 01 Aug 2017 08:03:48 GMT

Hi Sunny,

sorry, I Keep coming back here 😉 I'm going to create a new calendar here which shall also be a step towards a central calendar to be used everywhere which we don't have yet. I will take out the min and max Dates from the two tables which are of interest for this and autogenerate all the Dates between and fill in hour-intervals, too.

I think it will be better to open a new thread then - then I'll have to try again to calculate the min and max values for my slider_objects - I had some difficulties with that which were to do with the fact that I have data in one of my tables with a timestamp from days which are not in the other table - 'll have to think about ways to generate some dummy_data in one or both of the tables so that the timespans covered by each of those match the calendar.

Best regards,

DataNibbler

P.S.: Next time I'll open a new thread so this doesn't get too much.

Re: Double aggregation

datanibbler — Thu, 03 Aug 2017 12:59:45 GMT

Hi,

I don't get it - I have done some work on Scripting_level in the meantime and just now I'm back to the GUI.

I can calculate the max value in my Chart and set it for my slider and I can identify which month it has been found in using your formula. But I cannot do the same for the min value - I can calculate that, too, but I cannot identify the month that was found in.

My code is as follows:

>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

max(aggr(COUNT(DISTINCT Authenticated_User), Month))
& chr(10) &
FirstSortedValue(Month_Session, -aggr(COUNT(DISTINCT Authenticated_User), Month_Session))
[this works well]
min(aggr(COUNT(DISTINCT Authenticated_User), Month))
[this works, too]

& chr(10) &
FirstSortedValue(Month_Session, aggr(COUNT(DISTINCT Authenticated_User), Month_Session))

[this does not work]

The field "Month" is in my calendar while "Month_Session" is directly in the table where I also have the "Authenticated_User".

Can you make out why this does not work?

Thanks a lot!

Best regards,

DataNibbler