How to use a filter in selecting graph data to display

[#1]

Hi,

My first newbie question, I hope someone can help me

I have two sets of data in a table:

Masked Data

Unmasked Data

I also have a table with a single field 'Masking' and two possible values, 'masked' and 'unmasked'.  I created and loaded this table inline.

Now I have a graph, if 'masked' is selected in the filter, I want my X axis to be avg(Masked Data).

If 'unmasked' is selected, I want my X axis to be avg(Unmasked Data).

If nothing is selected, then I want the X axis to use avg(Masked Data).

What is the expression I would use to do this?

Thanks

[swuehl]

try a calculated dimension like

=[$(=If(Only(Masking) = 'unmasked', 'Unmasked Data','Masked Data'))]

[sunny_talwar]

UPDATE: I just saw that you wanted that when nothing is selected, you see Masked data. I think I would suggest using 'Always One Selected Value in which case you can always default to Masked when the application is opened. Might be something you want to reconsider.

UPDATE2: I have updated the sample in such a way that every time you press clear, the chart will show Masked Data. I did this by adding a clear state for the clear button, which will default  to Masked selection in the Selection field. Be cautious when setting the clear state because whatever selections you have made will be set when you set the clear state and the clear button will default to those selections. Ideally, I will clear out all selections except for Selection field and then set the clear state to make sure all else still behave as they would. To set clear state, look at the image below

Another approach could be like this:

Script:

Table:

LOAD * Inline [

Dim, Masked Data, Unmasked Data

A, 10, 20

B, 30, 10

C, 40, 20

A, 20, 32

B, 43, 12

C, 40, 23

];

Dim:

LOAD * Inline [

Selection

Masked

Unmasked

];

Chart Dimension -> I am using Dim, but you can continue to use whatever dimensions you have

Dim

Expression:

=Avg($(='[' & Selection & ' Data]'))

When you have Selected Masked in the Selection Filter, you will see the Avg of Masked

When unmasked is selected, you will see unmasked

The only thing to make sure here is that Selection has the option 'Always One Value Is Selected' checked out, because if it isn't, then there is a chance that the user might clear the selection in Selection or accidentally select both of them. In either of those cases, the chart would not be able to show any result.

Best,

Sunny

[sunny_talwar]

If you don't want to use 'Always One Selected Value', option and let the user clear out the selections. Then you can try this:

=If(GetSelectedCount(Selection) = 0, Avg([Masked Data]), Avg($(='[' & Selection & ' Data]')))

[#5]

Hi,

Thanks for the tips.

I should have mentioned that I am using Qlik Sense not Qlik view.

I will try the suggestions and report back.

[swuehl]

I've missed this part " I want my X axis to be avg(Unmasked Data)."

An axis should in general show something dimensional and not an aggregate (maybe you have some special needs, though).

But you can't just use an aggregation as dimension, you would need to use advanced aggregation to return at least point / line of the virtual table that the aggr() function creates:

=Aggr( Avg([$(=If(Only(Masking) = 'unmasked', 'Unmasked Data','Masked Data'))]), YourFieldToGroupTheAveragesBy)

and then you should define a measure for your chart.

I think it would be better if you can post a small sample QVF or some sample data records (e.g. in Excel sheets) and please elaborate further on your requested result chart.

[#7 · Accepted solution]

Thanks guys,

You all set me on the path to the right answer.  I'm afraid I cannot tick one as the Correct Answer as they all helped me get there.

What worked for me in the end was:

If(GetFieldSelections([Masking])='Unmasked',  Avg([Unmasked Data]),  Avg([Masked Data]))

I've also condensed by table into a single field with a single value of 'Unmasked'.  This makes it work as an on/off switch with the default as off.

I've just done the training but that was heavy on Tables and Load scripting and light on these expressions.  Thanks again for helping me fill in the gaps.

[sunny_talwar]

Although you might be finding it difficult to mark a single response as the correct one, but I would suggest to mark one which helped you more as the correct answer and you can always mark the other ones as helpful answer. Or You can mark you answer as correct, since that is what you used and mark our answer as helpful.

Qlik Community Tip: Marking Replies as Correct or Helpful

Marking helpful answer is optional, but marking correct answer is important as this will let other people know that the issue has been resolved and will save them the time to open this thread and realize that this has already been resolved.

Thanks and regards,

Sunny