$ Respect selections, except for two fields

[dena_reavis]

Hello Experts,

I need to respect user selections for all but two fields, Month and Quarter, and I need to specify other fields. I am trying to get a pivot table, with Months across the top to display all the months all the time, even if the user has selected one month or a quarter. I am using something like this:

sum({$<Datatype ={'Weekly'},

     [Process Type] = {'Hire'},

     Currency =,

     Month= {'*'} ,

     Qtr= {'*'}

>} EventCount)

I have also tried [Month]= , and Qtr= and it is not better. I think I know why this is... if the user comes to this chart with Q2 selected, that limits the months to April, May, and June, and I am using the $ set identifier, so yes, the chart will only show those three months. So, is there a way for me to use the $ and 1 as the set identifier?

Thank you!

[sunny_talwar]

Is Qtr the field which has the value Q2? If it is, then I don't see why it would show only three months if you are using this expression

Sum({$<Datatype ={'Weekly'}, [Process Type] = {'Hire'}, Currency, Month, Qtr>} EventCount)

This ignore selection in Qtr, Month and Currency field... no selection in these fields should filter your chart where you are using this expression.

Do you have a calculated dimension by any chance?

[dena_reavis]

Hi,

The only dimension in the chart is Month and it is not calculated. I did as you suggested and I have all but one of the expressions correct now. The only one that is not correct has different expression.

Only(Aggr(Above(Sum({$<Datasource ={'Monthly'},

     [Year Nbr] = ,

     Currency,

     [Forecast Type],

     [Month],

     [Qtr]

>} EventCount), -1), ThisMonthStart))

Any suggestion to make this ignore month and quarter?

Thank you!!

[sunny_talwar · Accepted solution]

Yes, I do.... you did not mention the use of Only function.... add your set analysis to Only function also

Only(

{$<Datasource ={'Monthly'},

     [Year Nbr] = ,

     Currency,

     [Forecast Type],

     [Month],

     [Qtr]

>}

Aggr(Above(Sum({$<Datasource ={'Monthly'},

     [Year Nbr] = ,

     Currency,

     [Forecast Type],

     [Month],

     [Qtr]

>} EventCount), -1), ThisMonthStart))

[dena_reavis]

Sorry Sunny, that one expression was the only one with an Only() function.

Thank you - as always, you are a tremendous help!!