AGGR Function where the structured Parameter needs ignore user selection

[TankerK]

Hello,

I am coming across a problem of setting the parameter to ignore user selection in the AGGR function. Taking you step by step:

- I am looking to calculate the median number of days that a customer has taken to return their library book. The dashboard allows the user to select what customer they want to deep dive into. However, I would also like to show the the average, median number of days, taken by a customer in their age group. The user can select the age group via a variable input drop down box.

- To do this, I have created the function:

AVG(AGGR(Median({1<ReturnedDate -= {''}, [AgeGroup] = {$(vAgeGroupSelected)}>} ReturnedDate - BorrowDate), Customer))

This calculates the median date per customer, puts this into a table/array, and then calculates the average of the corresponding results. I don't want the user's selection of customer to affect the average median number of days i.e. that should be calculated based on the age group selected, regardless of the user selection made on customer. I have therefore used the '1' set identifier

However, this function is susceptible to changes as different customer selections are changed.

- On the other hand, if I change the function to the below, the function doesn't change as different customer selections are changed. I'm not sure why this has impacted the results.

AVG({1}AGGR(Median({<ReturnedDate -= {''}, [AgeGroup] = {$(vAgeGroupSelected)}>} ReturnedDate - BorrowDate), Customer))

Can anyone shed any light on what is going on and how to achieve what I am setting out to do? I ultimately want to plot out the median time taken by a customer vs the avg. median time taken by their age group across time on a chart.

[pabloviera]

Hi, if you need to ignore the selection in the field Customer to get the AVG Median return days for an AgeGroup, you need to put the set modifier for customers in the AVG function like this:

AVG({<Customer>}AGGR(Median({1<ReturnedDate -= {''}>} Days), Customer))

And I don't think you need to have the AgeGroup in a variable, you can just make the users pick a value in the field. If you also want to show the KPI for all customers and all age groups, you just disregard the AG selection

AVG({<Customer,AgeGroup>}AGGR(Median({1<ReturnedDate -= {''}>} Days), Customer))

Regards

[TankerK]

Hi pabloviera,

TThanks for your reply. What does just inserting {<Customer>} do? I.e. what is the logic being applied here?

Thanks

[pabloviera]

Hi,

in Set Analysis you can put the fields without a set modifier (The '={'something'}' part) to omit any selection made in those fields.

So in the example you get an array of Median return days per Customer and then average it without taking into account if you have made a selection in the Customer field.

Regards

[TankerK]

Thank you Pabloviera - that makes sense!

I'm still having some trouble plotting this against time however. For example:

- The blue line is: Median({<ReturnedDate -= {''}, [AgeGroup] = {$(vAgeGroupSelected)}>} ReturnedDate - BorrowDate)

- The red line is:  Avg({<Customer>} AGGR(Median({<ReturnedDate -= {''}, [AgeGroup] = {$(vAgeGroupSelected)}>} ReturnedDate - BorrowDate), Customer))

Any idea why this is happening?

[pabloviera]

Without looking at your data,  I'm taking a guess that the AGGR function only populates the first appearance of a date and you need to use the nodistinct parameter to fill in all the data points

Take a look here https://community.qlik.com/t5/QlikView-App-Dev/What-NODISTINCT-parameter-does-in-AGGR-function/td-p/...

Regards