Double aggregation

[datanibbler]

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

[sunny_talwar · Accepted solution]

May be this

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

[datanibbler]

Yep!

Thanks Sunny! That works.

[datanibbler]

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 <<?

[antoniotiman]

Hi Friedrich,

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

Regards,

Antonio

[sunny_talwar]

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))

[datanibbler]

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 ...

[sunny_talwar]

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

[datanibbler]

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.

[datanibbler]

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.