make chart ignore current selections and display all data

[#1]

Hi all,

I'm trying to make an expression in a chart to force the chart to ignore one of my current data selections and return all data.

I believe I can use set analysis for this but I can't make it work.

The field I want to 'ignore' in my current selections is MONTHYEAR, so ive tried this in the dimension expression builder:

<MONTHYEAR={}>

But it doesn't work.

Thanks for any help.

[tabletuner]

This may help:

<MONTHYEAR={'*'}>

[#3]

Thanks but that doesn't work. In the expression builder it says 'garbage after ...' and in the chart it says //error in calculated dimension.

[#4]

Sum({1<Jahr={2006}, Region={Europa}>} Umsatz)

Take this example. If you select any other year or region, this expression does not change.So in your case you want to ignore the Monthyear so it must be

Sum({1<MonthJahr={...}, Region={Europa}>} Umsatz)

Here 1 means that the current selection is not taken. Forget the above example and just add 1 for your case

[#5]

If you want to respect other selections, then you don't want to use 1, that ingores all selections.

If you just want to ignore the selections on one field, use a Set Modifier without an Element Set:

Sum({<MONTHYEAR=>} Sales)

MONTHYEAR= as a Set Modifier ignores the selections in MONTHYEAR, but would respect other selections.

[#6]

Hi,

Thanks for your help, but I'm sorry I don't understand the syntax you've used. I'm new to set analysis.

Here is my sheet:

As you can see I have the month and year selected, which filters through to a master MONTHYEAR field which is a key field between tables.

I want the KPI summary to only display the current months data, but I want the lower KPI detail to show the history over time, hence multiple MONTHYEAR for the selected location. Here is my dimension screen and my expression builder screen for the MONTHYEAR dimension.

Thanks

Dan

[#7 · Accepted solution]

Set Analysis can only be used within an aggregate expression. In your dimension, you are using a Set Expression, but no aggregate function.

For your dimension, just use MONTHYEAR. There is no need for the calculated dimension there.

Then in your expressions, you want to use the Set Expression to ignore month year. For example if your first expression is Sum(Sales), then make it:

Sum({<MONTHYEAR=>} Sales)

Your first expression may be something like Sum(EmergencyWO), you would then use:

Sum({<MONTHYEAR=>} EmergencyWO)

You must do that in every expression.

Also, get rid of the 1, because that ignores all selections (unless that is what you want to do).

[#8]

Hi,

Ive done that to all 9 expression columns. It still only displays the selected month:

This is what I want it to do (as you can see i've manually deselected the month to get it to show the data;

Thanks

[#9]

Hi Daniel,

how is Month, Year and MONATHYEAR related? If you select a Month value you have to ignore this field. I don't know what the name of the Month Field in the data model is (the Field with "Jan","Feb","Mar"....) but if it's name is MONTH:

sum({MONTH=} EmerrgencyWO)

should work.

You have to put the exact field you would ignore in the set analysis.

Have Fun

Alex:)

[#10]

That works great thank you.

I was ignoring the MONTHYEAR field, which is the key field between tables, when I changed it to ignore the MONTH and YEAR fields it worked.