How can we have 2 diferente fields values with independent filter for each one in the same visual?

Question & Answer
, , , , , , , , ,
1

I have an analysis, which contains only one main filter at the control level. This filter has the purpose of being able to filter the name of the client; and the general exercise of this tab consists of being able to compare the values ​​of the filtered client vs the values ​​of the sector to which this client belongs.

In this tab I want to create a visual element where I can compare in the same graph, the value of the filtered client, and the value of the sector to which this client belongs (the sector is made up of a group of clients based on the classification that each of the clients has within the same table or dataset).

To be more specific with the exercise:

  • Graphic type: bars
  • Filter: specific client
  • Values ​​that we want to compare in a bar graph: [cost of sales margin] of the filtered client, [cost of sales margin] of the sector.
  • Grouped by years

This is what I want to get, but I haven’t been able to. Because I have a filter that I have up in QS that allows me to filter the client. But now how do I get the same value but in this case, for the Sector?

Chart QS

2

Hi @reyesnes,

Have you tried using LAC-W with PRE_FILTER to calculate the value at the sector level? When you use PRE_FILTER, the value won’t be affected by your client filter.

sumOver({cost of sales margin}, [Sector], PRE_FILTER)

1 Like
3

Hi @David_Wong , thank you very much for your reply.

This solution has not worked very well for me. The results are close, but they are not the correct results.

I have tried to understand how it is doing the results that it shows me, and I have already checked at the Rows level and the results must be different.

This is the result that I get directly in Quicksight:

chart_sales_cost_margin
chart_sales_cost_margin962×542 43.3 KB

The result for the client I am filtering is correct. However, the result for the sector to which the client belongs is wrong.

I have performed an export to Excel at table level from QuickSight itself, to obtain the results at raw level. And the results should be these:

excel_sales_cost_margin
excel_sales_cost_margin1546×1490 175 KB

And this is how the filters are configured:

chart_sales_cost_margin_filters
chart_sales_cost_margin_filters960×470 38.1 KB

And this is the formula to calculate what corresponds to the sector:

avgOver({__customer_margin_salesCost}, [{sector_economico}, {anno_fecha_corte_maxima_del_anno}], PRE_FILTER)

I would like to understand why this formula is not giving me the correct or expected result, according to how I show it in Excel?

4

Obviously, within the table there are also other clients with different sectors.

5

@reyesnes
Which of those filters do you want to be included in the calculation of the sector average? Your calculation is currently ignoring all of them because of the PRE_FILTER. Are you trying to include all those filters but not the client filter?

Can you include those fields in your table?
nombre_largo
es_fecha_corte_maxima_anno
fecha_corte

6

Hi @David_Wong

Yes, for de Sector Bars, I am trying to include all those filters but not the client filter.

Do you know how can I do this? Should this formula prevail, or should it be another one?

In the Excel Table Fields vs Quicksight Fields:

customer = nombre_largo field in QS
cut_date = fecha_corte field in QS

This field: es_fecha_corte_maxima_anno is only at filter level just in QuickSight.

What it does is filter the client’s last Financial Statement in the year.
Let’s suppose that a client can have up to 2 Financial Statements loaded in a year, but we are only interested in the last Financial Statement of each year. So, this is just a flag that tells you “yes” or “no”.

  • yes: validates that it is only the last financial statement of the year. (And this is the filter that prevails) :white_check_mark:
  • no: takes the rest of the client’s financial statements in the year.
7

@reyesnes

Can you try this? Disable/remove all your filters from your visual except for the client filter and put them in your calculated field. I’m assuming that FechaCorteDesdeMax3Meses and FechaCorteHastaMax3Meses are parameters.

Your calculated field should look like this (add any other filters that I missed):

avgOver(
    ifelse({es_fecha_corte_maxima_anno} = 't' AND {fecha_corte} <= ${FechaCorteDesdeMax3Meses} AND {fecha_corte} >= ${FechaCorteHastaMax3Meses} , {__customer_margin_salesCost}, null)
    [{sector_economico}, {anno_fecha_corte_maxima_del_anno}], 
    PRE_FILTER
)

After disabling/removing the filters from the visual, you need to add them to the calculated field for the client level average. Otherwise, it’s your client level average which will now be incorrect.

1 Like
8

Thank you very much @David_Wong . This has been very useful to me.

I have only made one modification to get the correct result:

avgOver(
    {__customer_margin_salesCost},
    [ifelse({es_fecha_corte_maxima_anno} = 't' AND {fecha_corte} <= ${FechaCorteDesdeMax3Meses} AND {fecha_corte} >= ${FechaCorteHastaMax3Meses} , {__customer_margin_salesCost}, null),
    {sector_economico}, {anno_fecha_corte_maxima_del_anno}, {es_fecha_corte_maxima_anno} = 't'],
    PRE_FILTER
)

I have added this other partition_field: {es_fecha_corte_maxima_anno} = 't'