topic Re: Exclude Selection from Expression in QlikView

Exclude Selection from Expression

alec1982 — Wed, 25 Nov 2020 16:16:04 GMT

hi all,

the following expression works fine for me and supposed to make calculation for specific name as added on the set analysis.

Now when I go on my list box "Name" and make different selection, it changes the numbers and give wrong values. how can I make sure the values dont change when I select a value on the Name column?

RangeSum(Above(

((Sum({$<Flag = {'1'}, [Name]= {'ABC'}>}[Price]) - above(Sum({$<Flag = {'1'}, [Name]= {'ABC'}>}[Price])))

/ ([Date] - above([Date]))

0.06)

([Date] - above([Date])) , 0,RowNo())))

Re: Exclude Selection from Expression

sunny_talwar — Fri, 08 Jul 2016 01:24:04 GMT

May be this:

RangeSum(Above(

((Sum({$<Flag = {'1'}, [Name]= {'ABC'}>}[Price]) - Above(Sum({$<Flag = {'1'}, [Name]= {'ABC'}>}[Price])))

/ (Only({<Name = >}[Date]) - Above(Only({<Name = >}[Date])))

0.06)

(Only({<Name = >}[Date]) - Above(Only({<Name = >}[Date]))) , 0, RowNo())))

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 01:32:21 GMT

Still giving wrong values when I select a different name. Shouldn't we add the name value on the added set analysis

Re: Exclude Selection from Expression

sunny_talwar — Fri, 08 Jul 2016 01:37:10 GMT

Well if without selection you are getting the right number, I wouldn't add the whole set analysis of Name = {'ABC'} to the Date portion.

I don't see any reason for this to not work. Can you share the screenshot of the expression? and also two screenshots of the objects with and without selection in Name field?

Re: Exclude Selection from Expression

sunny_talwar — Fri, 08 Jul 2016 01:40:24 GMT

Also there seems to be an extra parenthesis as the end...

=RangeSum(Above(

((Sum({$<Flag = {'1'}, [Name]= {'ABC'}>}[Price]) - Above(Sum({$<Flag = {'1'}, [Name]= {'ABC'}>}[Price])))

/ (Only({<Name = >}[Date]) - Above(Only({<Name = >}[Date])))

0.06)

(Only({<Name = >}[Date]) - Above(Only({<Name = >}[Date]))) , 0, RowNo()))

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 02:15:11 GMT

I think the issue is with the value where it shows as 0.06. this is not the actual value on the expression

I use a variable instead of the value and the variable is set to be

Set vYFactor =

if(

GetSelectedCount([X_Factor.Location])=0,

only({<[X_Factor.Location]={'Average'}>}[X_Factor.Storage Paid]),

only([X_Factor.Storage Paid])

the table with the factors info is a data island.

When I hard code the value and add for example 0.06 it works fine..

If I add the expression above instead of the hard coded value, I get the right info but if I make selection on the name, it gives wrong info

Re: Exclude Selection from Expression

sunny_talwar — Fri, 08 Jul 2016 02:31:02 GMT

Ignore Name here too then...

If(GetSelectedCount([X_Factor.Location]) = 0, Only({<[X_Factor.Location]={'Average'}. Name = >}[X_Factor.Storage Paid]), Only({<Name = >}[X_Factor.Storage Paid]))

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 02:37:25 GMT

even if use

if( GetSelectedCount([X_Factor.Location])=0, only({<[X_Factor.Location]={'Average'}>}[X_Factor.Storage Paid]), only([X_Factor.Storage Paid]) )

I get the right values if nothing is selected but wrong values if I select any Name.

Re: Exclude Selection from Expression

sunny_talwar — Fri, 08 Jul 2016 02:49:58 GMT

Give this a shot

If(GetSelectedCount([X_Factor.Location]) = 0, Only({<[X_Factor.Location]={'Average'}, Name = >}[X_Factor.Storage Paid]), Only({<Name = >}[X_Factor.Storage Paid]))

and see what you get

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 02:53:26 GMT

same issue...

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 03:36:42 GMT

Even replacing the it with just [X_Factor.Storage Paid] and make selection via UI.. doesn't work:(

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 03:40:13 GMT

Here is a screenshot of the current expression with the values in it

Re: Exclude Selection from Expression

qliksus — Fri, 08 Jul 2016 09:13:11 GMT

To narrow down the issue divide the exp is 2 parts

Exp1 :

RangeSum(Above(

((Sum({$<Flag = {'1'}, [Name]= {'ABC'}>}[Price]) - above(Sum({$<Flag = {'1'}, [Name]= {'ABC'}>}[Price])))

/ ([Date] - above([Date]))

Exp2 : If ( getselectedcount(X.factor) = 0 ,.......................)

Now select the Name and see which part is changing to narrow it down or plz attach a sample file

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 12:57:11 GMT

hi,

Thank you for your reply.

Do you mean create two measures on two columns?

Re: Exclude Selection from Expression

sunny_talwar — Fri, 08 Jul 2016 13:01:36 GMT

Would you be able to paste your expression as text? Would be easy to make changes to

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 13:08:14 GMT

Sure, and thanks for all the help:

if(rank(

RangeSum(Above(

((Sum({$<Third_Mature_Wed_Flag = {'1'}, [MetalName]= {'Aluminium'}>}[Spread Net Price]) - above(Sum({$<Third_Mature_Wed_Flag = {'1'}, [MetalName]= {'Aluminium'}>}[Spread Net Price])))

// ([Mature Date] - above([Mature Date]))

// (Only({<MetalName = >}[Mature Date]) - Above(Only({<MetalName = >}[Mature Date])))

/ (Only({<MetalName = {'Aluminium'}>}[Mature Date]) - Above(Only({<MetalName = {'Aluminium'}>}[Mature Date])))

If(GetSelectedCount([X_Factor.Location]) = 0, Only({<[X_Factor.Location]={'Average'}>}[X_Factor.Storage Paid]),

Only({<[X_Factor.Location]={'Average'}>}[X_Factor.Storage Paid])))

//0.053417)

(Only({< MetalName = {'Aluminium'}>}[Mature Date]) - Above(Only({<MetalName = {'Aluminium'}>}[Mature Date])))

0,RowNo()))) <= 5,

RangeSum(Above(

((Sum({$<Third_Mature_Wed_Flag = {'1'}, [MetalName]= {'Aluminium'}>}[Spread Net Price]) - above(Sum({$<Third_Mature_Wed_Flag = {'1'}, [MetalName]= {'Aluminium'}>}[Spread Net Price])))

/ (Only({<MetalName = {'Aluminium'} >}[Mature Date]) - Above(Only({<MetalName = {'Aluminium'}>}[Mature Date])))

If(GetSelectedCount([X_Factor.Location]) = 0, Only({<[X_Factor.Location]={'Average'}>}[X_Factor.Storage Paid]),

Only({<[X_Factor.Location]={'Average'}>}[X_Factor.Storage Paid])))

//0.053417)

(Only({<MetalName = {'Aluminium'}>}[Mature Date]) - Above(Only({<MetalName ={'Aluminium'} >}[Mature Date])))

0,RowNo())))

Re: Exclude Selection from Expression

sunny_talwar — Fri, 08 Jul 2016 13:12:53 GMT

How about this:

If(Rank(

RangeSum(Above(

((Sum({$<Third_Mature_Wed_Flag = {'1'}, [MetalName]= {'Aluminium'}>}[Spread Net Price]) - Above(Sum({$<Third_Mature_Wed_Flag = {'1'}, [MetalName]= {'Aluminium'}>}[Spread Net Price])))

/ (Only({<MetalName = {'Aluminium'}>}[Mature Date]) - Above(Only({<MetalName = {'Aluminium'}>}[Mature Date])))

If(GetSelectedCount([X_Factor.Location]) = 0, Only({<[X_Factor.Location]={'Average'}, MetalName = >}[X_Factor.Storage Paid]),

Only({<[X_Factor.Location]={'Average'}, MetalName = >}[X_Factor.Storage Paid])))

//0.053417)

(Only({< MetalName = {'Aluminium'}>}[Mature Date]) - Above(Only({<MetalName = {'Aluminium'}>}[Mature Date])))

0,RowNo()))) <= 5,

RangeSum(Above(

((Sum({$<Third_Mature_Wed_Flag = {'1'}, [MetalName]= {'Aluminium'}>}[Spread Net Price]) - above(Sum({$<Third_Mature_Wed_Flag = {'1'}, [MetalName]= {'Aluminium'}>}[Spread Net Price])))

/ (Only({<MetalName = {'Aluminium'} >}[Mature Date]) - Above(Only({<MetalName = {'Aluminium'}>}[Mature Date])))

If(GetSelectedCount([X_Factor.Location]) = 0, Only({<[X_Factor.Location]={'Average'}, MetalName =>}[X_Factor.Storage Paid]),

Only({<[X_Factor.Location]={'Average'}, MetalName =>}[X_Factor.Storage Paid])))

//0.053417)

(Only({<MetalName = {'Aluminium'}>}[Mature Date]) - Above(Only({<MetalName ={'Aluminium'} >}[Mature Date])))

0,RowNo())))

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 13:16:47 GMT

same issue.. if you need I can send you a join me to see it if that help

Re: Exclude Selection from Expression

sunny_talwar — Fri, 08 Jul 2016 13:18:16 GMT

I have a meeting in 15 minutes, may be I can come back to this at a later point

Re: Exclude Selection from Expression

alec1982 — Fri, 08 Jul 2016 13:20:03 GMT

that would be greatly appreciated.

Also, I sent you a message about accessing the test server. Let me know if you didnt get it

Thanks again