Filter in a dimension of a chart

[CezarioAbrantes]

I have created a dimension ('Rkg Rec Art 1') which filters the measures selecting the value '1' in the field 'Ranking Receita Artigo', as follows:

=Aggr(Only({<[Ranking Receita Artigo] = {'1'}>} [Artigo Completo]), [Artigo Completo])

It worked well in the tables, because I put 2 dimensions on them ('Ano-Mês' and 'Rkg Rec Art 1'), as you can see in the image "Table".

The problem is in the chart, where I can't put 2 dimensions as I've done in the tables. I tried to use the same idea to filter the field 'Ano Mês', using the expression below, but it didn't work, as you can see in the image "Chart".

=Aggr(Only({<[Ranking Receita Artigo] = {'1'}>} [Ano-Mês]), [Ano-Mês])

So how can I filter this dimension in the chart? Or is there another possibility that I don't know?

Thanks!

[andrew_smith200]

Can probably suggest something but need the data to try it out - posting up static .png files is no good for solutioneering. either post up- the data or inline code that creates the data.

[CezarioAbrantes]

Hi Andrew! Thanks for your suggestion. Please see attached all the files needed to run.

Just reminding, I need the chart to be the way it is now (month by month), just changing the %, in order to be equal to the table above (filtering only the value 1 in the field "Ranking Receita Artigo"). Now it is not filtering.

Thank you! 

[CezarioAbrantes]

Andrew, I forgot to tell you that the the chart that need to be changed is on the tab "Composição da Receita (1)".

[CezarioAbrantes]

If anyone else know how to help me, feel free! I'm really needing to do this. Thanks!

[lorenzoconforti]

If you want to keep the same structure, you will need to apply the set analysis to your measures. In the table, when you include the extra dimension there is a one-to-one relationship between "Ranking Receita Artigo" and "Artigo Completo"; on the other hand there is a one-to-many relationship between "Ranking Receita Artigo"  and "Ano-Mês"

To confirm that, if you apply your set analysis in the table on the "Ano-Mês" column and you remove the "Rkg Rec Art 1" column, you are going to get the same effect you get in the chart (i.e. it won't work)

The real issue for me is the data structure, how you've imported the data; apart from joining everything which is not necessary, you are keeping dimensions information encoded in the column names. You can see it clearly in the chart: you are creating 12 different measures instead of using a single dimension to get to those 12 different values

In summary, the only workaround to get it working in your current set up is to apply the set expression to each measure (after un-linking each measure to the master measure); see attached what I've done. The real solution is to get your data structured properly when importing it

[CezarioAbrantes]

Lorenzo, let me check if I got your point: the correct solution is to import all the measures in only one column, instead of in 12 columns?

If yes, what is the easiest way to do this: changing the Excel basis layout or is it possible to do it inside Qlik?

If it is possible do to inside Qlik, could you please explain how?

About doing the filter in the measures, it was the first way I thought about, but it is very laborious, because I'd need to do 30 tabs like this, so I should do 12 measures * 30 times.

Thanks a lot for your reply.

[lorenzoconforti · Accepted solution]

See attached; I've added an extra table to the data structure. You can see how the different columns from the excel file have been turned into two columns (one with the dimension name and the other with the value) using the crosstable function

I've added two charts to "Composição da Receita (1)" where you can see how much more straightforward is now to create bar charts and tables

[CezarioAbrantes]

Lorenzo, that's fantastic. Thank you very much for this. Could you please answer the following questions?

1. Is it possible to sort the measures inside "VendaLíquidaCategory"? The way it is, they are sorted by name, and maybe this is gonna be a problem.

2. About the CrossTable function, the first argument is always the dimension and the second is always the measure?

3. May I include as many CrossTables as I need in the script?

4. Why did you use the function "Total" in the scrip below?

=sum({<[Ranking Receita Artigo] = {'1'}>} [VendaLíquidaValue])/sum({<[Ranking Receita Artigo] = {'1'}>} Total <[Ano-Mês]> [VendaLíquidaValue]) 

Thank you again.

[lorenzoconforti · Accepted solution]

1. Yes, see attached; you can achieve this by using the match function as a sort expression

2. Correct; see here: https://help.qlik.com/en-US/sense/February2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

3. Yes

4. "Using the total qualifier inside your aggregation function disregards the dimensional value." https://help.qlik.com/en-US/sense/February2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/defin...