topic Re: current selection and aggr function in QlikView

current selection and aggr function

Thu, 24 Nov 2016 12:28:27 GMT

Hello,

I really need your help with current selection and aggr function.

The result should a table like

priority	Ø time in minute
1 very high	0,00
2 high	2,17
3 normal	17,59
4 low	8,81

This should show the average time per priority for all tickets are closed in the filters canonical month..

= sum(Aggr(only({$<DateType={'close'}, reaction_time_1stlevel= {"*"} ,createmonth=,create_time=,createdate=,createdatekompl=,createyear=, dwh_archive= {1}, category={'Incident' }>} reaction_time_1stlevel ),tn, Priorität))
/count({$<DateType={'close'}, createmonth=,create_time=,createdate=,createdatekompl=,createyear=, reaction_time_1stlevel= {"*"} , dwh_archive= {1},Kategorie={'Incident' } >} DISTINCT tn)

I had sum the reaction_time per ticket per priority and divide them by the number of tickets per priority.

There some filters too, like category or dwh_archive.

tn=ticketnumber

I implemented a master calender with Canonical_Date and chose the right one with Date_type “create“ or Date_Tpe “close”.

The problem is, that it should ignore the filter in create_month but it doesn’t.

I suspect the answer is obvious, but I cannot find it!

Thanks,for any help

Best regards
Sabine

Re: current selection and aggr function

sunny_talwar — Thu, 24 Nov 2016 12:33:13 GMT

May be add it to your outside Sum() function like this:

=Sum({<createmonth=, create_time=, createdate=, createdatekompl=, createyear=>} Aggr(Only({$<DateType={'close'}, reaction_time_1stlevel= {"*"}, createmonth=, create_time=, createdate=, createdatekompl=, createyear=, dwh_archive= {1}, category={'Incident' }>} reaction_time_1stlevel ), tn, Priorität))
/Count({$<DateType='close'}, createmonth=, create_time=, createdate=, createdatekompl=, createyear=, reaction_time_1stlevel= {"*"}, dwh_archive= {1}, Kategorie={'Incident' }>} DISTINCT tn)

Re: current selection and aggr function

Thu, 24 Nov 2016 12:39:46 GMT

Hi Sunny,

this was a really fast solution.

Many thanks, it solved my problem.

Thank you very much!!

Re: current selection and aggr function

sunny_talwar — Thu, 24 Nov 2016 12:41:28 GMT

Awesome!! Please close the thread by marking the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny