Ignore filter selection of a field being used in aggregation expression

[therealdees]

Hi!

I'm trying to make a line chart ignore a month filter (it's a period field from a master calendar), but I can't get it to work. I tried using something like {<month = >} and month =::$ and still no success. Thought maybe I could use alternate states?

The original expression is:

Count(Distinct {<erp_venda_devolucao_flag = {'V'}, app_soma_nf_flag = {'S'}, cliente_dias_ausente -= {''}, key_cliente_fornec -= {'', '1'}, cliente_fornec_natureza = {'Física'}>} erp_nf_completo)
/
Aggr(Count(Total <mes> Distinct ${< mes =, erp_venda_devolucao_flag = {'V'}, app_soma_nf_flag = {'S'}, key_cliente_fornec -= {'', '1'}>} erp_nf_completo), mes, intervalo_retorno)

PS: mes = month

The expression is being used to calculate the counting for different intervals of returning days by customer. It's aggregated by month and interval.

Any help is appreciated.

[Oleg_Troyansky]

Hi,

This is a very tricky issue, and it's related to the use of AGGR().

Fundamentally, AGGR() returns an array of values, not a single value. For that reason, AGGR() can only be used without an outer aggregation function (such as sum(AGGR(...))  ) in Chart Dimensions, where an array of numbers is expected.

In Measures, however, a single number is expected, hence you should be using an aggregation function around your AGGR(). In the absence of an aggregation function, Qlik uses the default aggregation function Only(), so your expression really means the following:

Count(Distinct {<erp_venda_devolucao_flag = {'V'}, app_soma_nf_flag = {'S'}, cliente_dias_ausente -= {''}, key_cliente_fornec -= {'', '1'}, cliente_fornec_natureza = {'Física'}>} erp_nf_completo)
/
Only(   
             Aggr(Count(Total <mes> Distinct ${< mes =, erp_venda_devolucao_flag = {'V'}, app_soma_nf_flag = {'S'},                    key_cliente_fornec -= {'', '1'}>} erp_nf_completo), mes, intervalo_retorno)

)

Now, this external function Only() doesn't have any Set Analysis, and that's what causes issues with the internal Set Analysis within your Count() function.

So, you have two options - either find a way to eliminate AGGR() from your calculation (I don't think you need it at all, but I don't know all the details about your calculation), or add an outer aggregation function (sum, count, or even Only), and copy/paste the same set analysis from the inner aggregation into the outer aggregation.

I'm teaching a similar example in my detailed session on Set Analysis and AGGR at the  Masters Summit for Qlik - coming soon to Orlando and to Dublin! Come and learn advanced development techniques from the best experts in the world!

Cheers,

 

[therealdees]

Hi, Oleg!

It makes sense, thanks for explaining.

In this case:

erp_venda_devolucao_flag, app_soma_nf_flag, cliente_fornec_natureza = flags to identify valid sales that should measured.

key_cliente_fornec = the customer id

erp_nf_completo = sale transaction id

cliente_dias_ausente = I added this column in the loader by ordering the table by customer id and purchase date, then calculated the days difference between the last and last but one purchase by customer so I could calculate the customer return rate.

intervalo_retorno = I created a interval table holding different intervals of days that I'm using to aggregate count

mes = month

I'm not sure either if I need to use Aggr() in my scenario, but it was the only way I got it to work to display the chart in stacked area by interval (as print below). First I count every distinct sale_id that has a days_difference value present (the value is loaded only if it meets some conditions) and that gives me the total count of sales by customers that had shopped previously (not a new customer). Then I aggregate the counting of sales in general (returns or new customers) by interval and month by using the interval as dimension to finally divide returns/sales and obtain the different intervals stacked percentages.

In another tab I simply display the total rate without the aggregations and using a ValueList as a dimension to display a single aggregated value by using this expression:

Count(Distinct {<app_soma_nf_flag = {'S'}, erp_venda_devolucao_flag = {'V'}, key_cliente_fornec -= {'', '1'}, cliente_dias_ausente -= {''} >} erp_nf_completo)
/
Count(Distinct {<erp_venda_devolucao_flag = {'V'}, app_soma_nf_flag = {'S'}>} erp_nf_completo)

Everything seems to be correct and be working fine so far, except I can't make the line chart ignore the month selection as it's a calendar field that is being used as a dimension in the chart as well and aggregated in one of the expressions.

How can I remove the aggregation in this situation? Do you think there's a better approach to the same objective?

PS: Neither expressions are ignoring the month filter correctly. It either affects the values in the chart or display only the selected months in the filter.

[therealdees]

It's funny... I tried to follow your suggestion of aggregating outside and using the same set analysis with the following expression:

Sum(Aggr(Count(Distinct {<app_soma_nf_flag = {'S'}, erp_venda_devolucao_flag = {'V'}, key_cliente_fornec -= {'', '1'}, cliente_dias_ausente -= {''} >} erp_nf_completo), key_cliente_fornec))
/
Sum(Distinct {<erp_venda_devolucao_flag = {'V'}, app_soma_nf_flag = {'S'}>} Aggr(Count(Total <mes> Distinct {<erp_venda_devolucao_flag = {'V'}, app_soma_nf_flag = {'S'}>} erp_nf_completo), intervalo_retorno, mes))

If I involve the expressions in "$(=)" to see the output calculation below in the script window it actually shows me the right calculation for both numbers and the division of those numbers give me the correct return rate, but in the chart it calculates differently for some reason...

[therealdees · Accepted solution]

Finally got it!!

I applied an alternate state to container that has the 2 line chart tabs, then I used the alternate state group in the set analysis and set the year as the only field to consider as default state, so whenever I selected a year it will still impact the chart, but that is not true for the months. I never had used alternate states before, I confess it's pretty useful. For those that might be useful for, the expressions are:

Count(Distinct {DashboardRetorno_HistoricoTaxa<year= $::year, flag_1= {'S'}, flag_2= {'V'}, customer_id-= {'', '1'}, days_difference-= {''} >} sale_id)
/
Aggr(Count(Total <month> Distinct {DashboardRetorno_HistoricoTaxa<year= $::year, flag_1= {'V'}, flag_2= {'S'}>} sale_id), days_interval, month)

Thanks for the help Oleg.