topic Re: average Aggr function in QlikView

average Aggr function

samuel_brierley — Tue, 27 Oct 2015 10:41:16 GMT

Hi all,

can someone please take a look at the below,

the expressions belongs in a table with the following dimensions

Weekday(Date)

if(Time,>12:00,'PM','AM')

the expressions should count all the minutes for each day and then average that table placing the values in the appropriate cell depending on the weekday and time.

=avg(aggr(

count({$< ArbitrationInput.Group.rOOk = {"<$(=only({$< [/rec/parameter] = {'rOff'},[/#text] = {'CommonParameters'},[Config] = {'Live'}>}[/rec/parameter/curval]))"} >} Timestamp),

Date))

what seems to happen in when i dont filter only the AM is shown unless i filter to PM. i think something is wrong with the time element and all data is getting lumped into the AM unless i specify otherwise.

thanks

Re: average Aggr function

rubenmarin — Tue, 27 Oct 2015 10:44:59 GMT

Hi samuel, you can try using it as number in the if, 12:00 is half a day so it's numerac representation is '0.5':

if(Time>0.5,'PM','AM') // '>' counts 12 o'clock as 'AM', '>=' will count 12 o'clock as 'PM'

Re: average Aggr function

samuel_brierley — Tue, 27 Oct 2015 10:46:39 GMT

thanks for the reply Rubin however the problem is the same

Re: average Aggr function

rubenmarin — Tue, 27 Oct 2015 10:49:27 GMT

I have doubts if I understand it right, anyway you can try to add your dimensions to the Aggr:

=avg(aggr(

count({$< ArbitrationInput.Group.rOOk = {"<$(=only({$< [/rec/parameter] = {'rOff'},[/#text] = {'CommonParameters'},[Config] = {'Live'}>}[/rec/parameter/curval]))"} >} Timestamp),

WeekDayField, Date, AM/PM_Field))

Re: average Aggr function

swuehl — Tue, 27 Oct 2015 10:53:07 GMT

I think you have a grain mismatch here

Pitfalls of the Aggr function

Chart dimension Time is finer than aggr() dimension Date. You can try with an NODISTINCT qualifier to the aggr() function, but not sure if this will return correct values.

Re: average Aggr function

samuel_brierley — Tue, 27 Oct 2015 11:06:46 GMT

thanks swuehl thats certainly getting numbers through.

i feel a few hours of info checking are required but thanks

Re: average Aggr function

JonasValleskog — Tue, 27 Oct 2015 16:02:25 GMT

Hi Samuel,

My educated guess is that your Time field actually contains a timestamp, i.e. behind the scenes it's tracking both the date and the time. It's impossible to tell from what you've shared so far, it's just a hunch...

In your script, if I'm right and you actually have a timestamp, you can create a true Time field like this:

LOAD

...

Time(TimestampField-dayStart(TimestampField)) AS Time,

...

If on the other hand QlikView isn't recognising your Time properly, it may be a text field. You can test this by the IsNum() function. Any properly interpreted time,timestamp or date would return 'True'. In that case, look at the definition of the Time#() function which allows you to easily convert a text time to a true time field.

AM/PM

If you pre-calculate your AM/PM dimension in the script, things will be a lot easier to debug and you will deliver better performance rather than calculating this attribute unnecessarily on the fly (it's a static attribute, so no need for a front end calculated dimension).

So, in the script, where ever the Time field sits, you can add an extra row like this:

LOAD

...

if(Time>time#('12','hh'),'PM','AM') AS [AM/PM]

...

I hope it helps.

Cheers

Jonas