topic Set analysis not working properly in QlikView

Set analysis not working properly

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

Hi all

I have this expression which i can't get to work properly,

Sum({<FrontLossFlag ={'*'} - {'1'}, DataSource = {'*'} - {'DPW' , 'DEX (DPW)' }>} Real)

If I remove either 'DPW' or 'DEX (DPW)', then it works. But not if both a left inside the expressions, then it return the value of zero, which isn't correct.

I can't seem to figure what I'm doing wrong? I've also tried inserting '[ DEX (DPW) ] ', however this doesn't return the correct value either.

I hope someone can help

Regards

Stefan

Re: Set analysis not working properly

tomovangel — Thu, 20 Jul 2017 07:19:07 GMT

Have you tried using Aggr() ?

Re: Set analysis not working properly

antoniotiman — Thu, 20 Jul 2017 07:19:35 GMT

Why not

DataSource -= {'DPW' , 'DEX (DPW)' }

Re: Set analysis not working properly

Anil_Babu_Samineni — Thu, 20 Jul 2017 07:20:42 GMT

What if you are trying something like below

Sum({<FrontLossFlag -= {'1'}, DataSource -= {'DPW' , 'DEX (DPW)' }>} Real)

Note - I assume DEX (DPW) is not valid name. Will you check same

Re: Set analysis not working properly

Kushal_Chawda — Thu, 20 Jul 2017 07:22:00 GMT

you can replace the key word in script like below and then use it in set analysis

LOAD *,

if(DataSource ='DEX (DPW)','DEX_DPW',DataSource ) as DataSourceNew

Sum({<FrontLossFlag ={'*'} - {'1'}, DataSourceNew= {'*'} - {'DPW' , 'DEX_DPW' }>} Real)

Re: Set analysis not working properly

Anonymous — Thu, 20 Jul 2017 07:29:35 GMT

Hi Antonio

I've tried that also, and it doesn't work.

Re: Set analysis not working properly

Anonymous — Thu, 20 Jul 2017 07:32:22 GMT

Hi Anil

I've tried above alsp and it doesn't work.

If I make the expressions as none set analysis then it work.

Sum(if(FrontLossFlag <> '1' AND DataSource <> 'DPW' AND DataSource <> 'DEX (DPW)' , Real))

So DEX (DPW) is a valid name.

Re: Set analysis not working properly

Thu, 20 Jul 2017 07:32:40 GMT

It's pretty difficult to solve these problems without more information and some sample data.

You need to isolate the issue. Can you first confirm that this works in isolation and filters just for the values you want to exclude:

Sum({<DataSource ={'DPW' , 'DEX (DPW)' }>} Real)

Sum({<FrontLossFlag ={'1'}>} Real)

If so, then perhaps you could try something like this:

Sum({

<FrontLossFlag =, DataSource =>

<FrontLossFlag ={'1'}, DataSource = {'DPW' , 'DEX (DPW)' }>} Real)

Re: Set analysis not working properly

Anonymous — Thu, 20 Jul 2017 07:33:28 GMT

Hi Kushal

I would prefer not scripting me out of this.

Re: Set analysis not working properly

Anonymous — Thu, 20 Jul 2017 07:35:13 GMT

Hi Angel

No I haven't, would you do that?

Re: Set analysis not working properly

Anil_Babu_Samineni — Thu, 20 Jul 2017 07:36:09 GMT

Interesting, Condition should work. We must need some UAT for Real values. Will you provide sample application

Re: Set analysis not working properly

antoniotiman — Thu, 20 Jul 2017 07:36:39 GMT

Can You provide some sample data ?

Re: Set analysis not working properly

Kushal_Chawda — Thu, 20 Jul 2017 07:54:12 GMT

try this

sum({<FrontLossFlag ={'*'} - {'1'}, DataSource = {'*'} - {'DPW' , '$(='DEX ' & chr(40)&'DPW' &chr(41))'}>} Real)

Re: Set analysis not working properly

Anil_Babu_Samineni — Thu, 20 Jul 2017 08:16:59 GMT

Kushal Bhai,

Why are you using Chr() over here. Even, We can direct use this DEX (DPW) right??? Just to know

Re: Set analysis not working properly

Anonymous — Thu, 20 Jul 2017 08:28:28 GMT

I have hard time providing a sample data right now.

However, if I split the expression up as below I can get it to work.

Sum(Real)

-Sum({<FrontLossFlag ={'1'}>} Real)

-Sum({<DataSource ={'DPW'}>} Real)

-Sum({<DataSource ={'DEX (DPW)'}>} Real)

It definitely seems like this part DataSource ={'DPW' , 'DEX (DPW)' } of the expression i causing the problem. But only when these two are put together {'DPW' , 'DEX (DPW)' }.

Re: Set analysis not working properly

Thu, 20 Jul 2017 08:32:08 GMT

Doesn't solve the root problem, but at least you can have it working in one expression with something like this:

Sum({

<FrontLossFlag =, DataSource =>

<FrontLossFlag ={'1'}, DataSource = {'DPW'}>

<DataSource = {'DEX (DPW)' }>

}

Real)

Re: Set analysis not working properly

Anil_Babu_Samineni — Thu, 20 Jul 2017 08:36:21 GMT

What i said may root cause, Will you take these filters FrontLossFlag, DataSource and Select 1 from FrontLossFlag and select remaining two names from DataSource and then create Text box with Sum(Real) then tell me what you are getting. Meanwhile clear those and use below condition in text box

Sum({<FrontLossFlag ={'1'}, DataSource = {'DPW','DEX (DPW)'}>}Real)

PS - your question not in the list but you are concluding the with iCn the list filter. Hence check opposite if first thread is the correct post.

Re: Set analysis not working properly

Anonymous — Thu, 20 Jul 2017 08:58:53 GMT

I get a number when I use the filters and Sum(Real) in textbox.

If clear the filters and insert Sum({<FrontLossFlag ={'1'}, DataSource = {'DPW','DEX (DPW)'}>}Real), then the value Zero i shown.

Re: Set analysis not working properly

Anil_Babu_Samineni — Thu, 20 Jul 2017 09:01:21 GMT

Now, Time to have sample application

Re: Set analysis not working properly

Kushal_Chawda — Thu, 20 Jul 2017 09:24:26 GMT

This not the big script,just a simple condition, you can try to check this out