creating a table that is already filtered on a specific value from a dimension

[#1]

hi,

i would like to create a table that is already filtered by a specific value from a specific dimension.

for example, i want a table that shows the income from a specific customer (from the customers dimension), and that is dedicated only to that customer (because it's the biggest, and deserves a table of its own).

my real reason is a bit different, but the example above is close enough, and much simpler to understand.

thank you,

Yaron.

[Gysbert_Wassenaar]

You can use set analysis expressions. For example Sum(Amount) would become Sum({<Customer={'Joe'}>}Amount) if you only want to show data for customer Joe. You'd have to add {<Customer={'Joe'}>} to each expression in your table.

[datanibbler]

Hi yaron,

I take it you want to do that in the script?

Do you already have that value (that customer) at that point?

That, of course, is a prerequisite - there is no way to "feed back" anything from the GUI into the script - generally speaking.

You can, of course, put the value to filter for in an Excel_file and load that before actually creating that table, so you can use it in a filtering_clause already.

Depending on which way your app is used (browser or "full" client_Software), you could even determine that on the GUI and implement a button to first export that into an Excel file and then reload, thereby using that Excel file and creating that dedicated table.

HTH

Best regards,

DataNibbler

[#4]

i would like to do that from the UI not the script (though if that's the only option i'll do it from the script).

yes, i already have that value.

all of the data for all the customers is loaded anyway.

i just want to save my users from constantly filtering on the same value, when they can get a table that is already filtered on that value.

[datanibbler]

Hi yaron,

well, use a trigger then (either on sheet_level (in the properties_dialog) or on the button, if you have any, that is used to activate that sheet.

[Anonymous]

That is certainly correct.  But, if there are multiple expressions, it may be better to use calculated dimensions, e.g.

=only({Customer={'Joe'}>} Customer)

and, check "suppress when value is null".  Expressions will be unchanged, e.g. Sum(Amount).

[datanibbler]

Hi Michael,

calculated dimensions are a good way to do things like that.

I'd only like to add that if you choose to do that, it's important to make sure the user knows why he is seeing a table that only shows values for that one customer. It might well have been that same user who asked for that table, but by my experience, people can still be surprised if you give them what they asked you for 😉

Best regards,

DataNibbler

[Anonymous]

"...people can still be surprised..."

Yes, be careful what you wish for.  Anyway a little help text or just an informative chart caption will be helpful:

"Chart for Customer Joe"

[Gysbert_Wassenaar]

I'm a bit wary of calculated dimensions because of the possible impact on performance.

[Anonymous]

I understand.  From the performance point, it is better to have a separate filed, e.g. "Customer Joe".