topic Re: $ Respect selections, except for two fields in QlikView

$ Respect selections, except for two fields

dena_reavis — Tue, 23 Jan 2018 00:34:20 GMT

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!

Re: $ Respect selections, except for two fields

sunny_talwar — Tue, 23 Jan 2018 03:17:48 GMT

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?

Re: $ Respect selections, except for two fields

dena_reavis — Tue, 23 Jan 2018 19:52:20 GMT

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!!

Re: $ Respect selections, except for two fields

sunny_talwar — Tue, 23 Jan 2018 19:55:33 GMT

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))

Re: $ Respect selections, except for two fields

dena_reavis — Tue, 23 Jan 2018 21:50:21 GMT

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

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