topic Re: Need Help for an Intersection Problem in Set Analysis in QlikView

Need Help for an Intersection Problem in Set Analysis

Anonymous — Wed, 18 Jun 2014 09:29:55 GMT

Hi at all,

i have a problem with an intersection in Set Analysis.

I have one Table with Parts, Prices and Factorys.

I need the difference beetween the Prices in two different Factories for the Same Part.

How is the correct Expression with Aggr in a Text Field?

Details in the Example (.qvw)

Re: Need Help for an Intersection Problem in Set Analysis

MK_QSL — Wed, 18 Jun 2014 09:41:01 GMT

=IF(sum({<Factory= {'200'}>} Price) > sum({<Factory= {'100'}>} Price), sum({<Factory= {'200'}>} Price)- sum({<Factory= {'100'}>} Price))

=Aggr(IF(sum({<Factory= {'200'}>} Price) > sum({<Factory= {'100'}>} Price), sum({<Factory= {'200'}>} Price)- sum({<Factory= {'100'}>} Price)),Part, Factory)

Re: Need Help for an Intersection Problem in Set Analysis

tresB — Wed, 18 Jun 2014 09:42:13 GMT

=If( (sum({<Factory= {'200'}>} Price) -sum({<Factory= {'100'}>} Price))>0,sum({<Factory= {'200'}>} Price) -sum({<Factory= {'100'}>} Price))

PFA

Re: Need Help for an Intersection Problem in Set Analysis

mdmukramali — Wed, 18 Jun 2014 09:45:24 GMT

Dear ,

can you try like

=if(sum({<Factory= {'200'}>} Price)>sum({<Factory= {'100'}>} Price),(sum({<Factory= {'200'}>} Price))-(sum({<Factory= {'100'}>} Price)))

Re: Need Help for an Intersection Problem in Set Analysis

Anonymous — Wed, 18 Jun 2014 10:17:35 GMT

yes thank you. but i need an aggr expression in a textbox for the result

only if PART exist in Factory 100 and PART exist in Factory 200 then make the Price Difference #

the result is the SUM: in this exampe 10+4 = 14

Re: Need Help for an Intersection Problem in Set Analysis

MK_QSL — Wed, 18 Jun 2014 10:22:58 GMT

=SUM(Aggr(IF(sum({<Factory= {'200'}>} Price) > sum({<Factory= {'100'}>} Price), sum({<Factory= {'200'}>} Price)- sum({<Factory= {'100'}>} Price)),Part, Factory))

Re: Need Help for an Intersection Problem in Set Analysis

Anonymous — Wed, 18 Jun 2014 11:11:14 GMT

thank you, but the result of your expression is "175" ... the correct result is 14 (screenshot)

Re: Need Help for an Intersection Problem in Set Analysis

MK_QSL — Wed, 18 Jun 2014 11:26:40 GMT

Can you elaborate little more what are expecting ?

Thanks

Re: Need Help for an Intersection Problem in Set Analysis

Anonymous — Wed, 18 Jun 2014 11:37:05 GMT

I need the price difference beetween the parts who exist in Factory 100 and Factory 200.

Example:

the part 666 and 777 exist in both Factorys but in factory 200 is the part 666 and 777 more expensive.

i need an expression for following conditions:

- Parts are the SAME - Part exist in both factorys

- If in Factory 200 the price is higher than in Factory 100 then i need the sum

for this example is 14 (see screenshot above)

Re: Need Help for an Intersection Problem in Set Analysis

MK_QSL — Wed, 18 Jun 2014 12:40:01 GMT

Use this...

=IF(SUM({<Factory = {'200'}, Part = P({1<Factory = {'100'}>})>}Price) > SUM({<Factory = {'100'}, Part = P({1<Factory = {'200'}>})>}Price),

SUM({<Factory = {'200'}, Part = P({1<Factory = {'100'}>})>}Price) - SUM({<Factory = {'100'}, Part = P({1<Factory = {'200'}>})>}Price))

Re: Need Help for an Intersection Problem in Set Analysis

miikkaqlick — Wed, 18 Jun 2014 14:19:15 GMT

Hi!

Here is a solution for you:

Diff:

Sum({$<Factory = {'200'}, Part = P({<Factory= {'100'}> }) * P({<Factory= {'200'}>}) >} Price)

Sum({$<Factory = {'100'}, Part = P({<Factory= {'100'}> }) * P({<Factory= {'200'}>}) >} Price)

Br,

Miikka Koskinen