topic Re: Excluding filters in Set Analysis in QlikView

Excluding filters in Set Analysis

supriyabiware — Wed, 31 Jan 2018 12:55:47 GMT

Hello All,

i need to exclude multiple filters while calculating the below value

This doesnt work -

vExp_BARWERT=

((sum($(vField_Barwert_Angebote))*Avg(Quote/100))*(Sum($(vField_Bedarf))/Sum(total {<[Werk Bezeichnung]=,Commodity =,Werk=,[Anfragende Marke]=>}$(vField_Bedarf))))

This Works-

vExp_BARWERT=

((sum($(vField_Barwert_Angebote))*Avg(Quote/100))*(Sum($(vField_Bedarf))/Sum(total {<[Werk Bezeichnung]=>}$(vField_Bedarf))))

How can i exclude multiple filters in the set analysis? i am using $(vExp_BARWERT) in the chart

Thanks

Supriya

Re: Excluding filters in Set Analysis

Anil_Babu_Samineni — Wed, 31 Jan 2018 12:58:20 GMT

If second one works, I believe first one may work.

Re: Excluding filters in Set Analysis

sunny_talwar — Wed, 31 Jan 2018 13:03:34 GMT

When you say it doesn't work, what exactly do you mean? You get error? Unexpected result? or what is wrong?

Re: Excluding filters in Set Analysis

supriyabiware — Wed, 31 Jan 2018 13:16:17 GMT

i am trying to calculate yearly Barwert from normal one and Scale it-

vExp_Barwert_Yearly=

(sum($(vField_Barwert_Angebote))*Avg(Quote/100))*(Sum($(vField_Bedarf))/Sum(total {<[Werk Bezeichnung]=,Commodity =,Werk=,[Anfragende Marke]=>}$(vField_Bedarf)))

Result -

vExpBarwert_Yearly_Total=

(sum(total <Werk,[Werk Bezeichnung],[Anfragende Marke],Commodity> $(vField_Barwert_Angebote))*Avg(Quote/100))

Result

Scalenumber =

if(alt($2,$1)>=1000000000, num($1/1000000000, '#.##0,00'),

if(alt($2,$1)>=1000000, num($1/1000000, '#.##0,00'),

if(alt($2,$1)>=1000, num($1/1000, '#.##0,00'),

num($1, '#.##0,00'))))

Final =$(vScaleNumber($(vExp_Barwert_Yearly), $(vExp_Barwert_Yearly_Total)))

but when i remove the multiple filter exclusion and make below equation as -

vExp_Barwert_Yearly=

(sum($(vField_Barwert_Angebote))*Avg(Quote/100))*(Sum($(vField_Bedarf))/Sum(total {<[Werk Bezeichnung]=>}$(vField_Bedarf)))

something is going wrong with vExp_Barwert_Yearly calculation - is there any other way?

Thanks

Supriya

Re: Excluding filters in Set Analysis

sunny_talwar — Wed, 31 Jan 2018 13:22:28 GMT

So, this is a known issue.. because the delimiter for paramterized variable is comma, the comma you are using in your set analysis is seens as different arguments for the variable. I have seen marcus_sommer‌ provide a solution for this, may be he can provide it yet again here as I don't really remember what he has suggested in the past

Re: Excluding filters in Set Analysis

marcus_sommer — Wed, 31 Jan 2018 14:45:34 GMT

Sunny is right and the commas within the total-variable will be treated as parameter-delimiter. I think if you replaced the commas there maybe with '|' and then reversing it again by calling the $2 within the variable Scalenumber it could be bypassed.

Whereby the approach of such bypassing is quite ugly and error-prone and I wouldn't really suggest them else I would try to avoid these comma-issue completely. One way could be just not using such complex constructs in variables because the aim of the variables is to simplify the developer-life and not the opposite and therefore it could be the easier way just to use normal expressions.

Whereby if I think again a doubled $-sign expansion might be also working in this case. This meant the following call:

=$(vScaleNumber($(vExp_Barwert_Yearly), $(=$(vExp_Barwert_Yearly_Total))))

- Marcus