topic Re: Multiple alternate states in single expression in QlikView

Multiple alternate states in single expression

vipingarg23 — Thu, 01 Mar 2018 08:16:03 GMT

Hi Team,

I need to use below formula with alternate states

Sum(A*B)

where A is coming from State1 and B is coming from State2.

Is it possible to use two alternate state in single sum function?

Re: Multiple alternate states in single expression

sunny_talwar — Thu, 01 Mar 2018 08:23:43 GMT

Try to see if this works

Sum(Aggr(Only({State1} A)*Only({State2} B), UniqueIdentifierField))

Re: Multiple alternate states in single expression

vipingarg23 — Thu, 01 Mar 2018 08:29:29 GMT

No..it is not working..

Re: Multiple alternate states in single expression

sunny_talwar — Thu, 01 Mar 2018 11:20:19 GMT

Would you be able to share a sample to look at?

Re: Multiple alternate states in single expression

vipingarg23 — Thu, 01 Mar 2018 13:22:33 GMT

Please find the sample file..

I want Sum(A*B)

A is from test1 and B is from test2

Re: Multiple alternate states in single expression

vipingarg23 — Thu, 01 Mar 2018 13:28:02 GMT

There is listbox FY...One Fy has alternate state test1 and another one has test2. For example

FY with test1 is 2017

FY with test2 is 2016

Now column A should take value for FY 2017 (test1) and Column B should take value for FY 2016(test2). Then it should apply the formula:

Sum(A*B)

Re: Multiple alternate states in single expression

sunny_talwar — Thu, 01 Mar 2018 13:29:41 GMT

So, based on the selection you have given... what is the expected output here?

Re: Multiple alternate states in single expression

vipingarg23 — Thu, 01 Mar 2018 15:36:41 GMT

Expected Output Should be 196..

Re: Multiple alternate states in single expression

vipingarg23 — Thu, 01 Mar 2018 15:39:54 GMT

If you can tell me any other way to solve this, then also I am fine..

Re: Multiple alternate states in single expression

sunny_talwar — Thu, 01 Mar 2018 16:29:20 GMT

How are you calculating this number 196? Can you walk me through the logic?

Re: Multiple alternate states in single expression

vipingarg23 — Thu, 01 Mar 2018 16:46:33 GMT

Sorry..It should be 159..

Value for Column A For FY 2017 = 5,7

Value for Column B For FY 2016 = 15 12

So (5*15)+(7*12) = 75+84=159

Re: Multiple alternate states in single expression

sunny_talwar — Thu, 01 Mar 2018 16:49:03 GMT

How did you choose to multiply 5 with 15 and 7 with 12... why not (5*12) + (7*15)... this is what I am trying to understand here? Is there a common dimension between 5 and 15 and 7 and 12?

Re: Multiple alternate states in single expression

vipingarg23 — Thu, 01 Mar 2018 17:02:41 GMT

Yes..You are right..I didn't take the dimensions in sample...But it will choose according to common dimensions.

Re: Multiple alternate states in single expression

sunny_talwar — Thu, 01 Mar 2018 17:06:27 GMT

This works

=Sum(Aggr(Sum({$*test1}A)*Sum({$*test2}B), CommonDim))

Added the commondim in your sample to show how it will work

LOAD * INLINE [

FY, A, B, CommonDim

2016, 13, 15, 1

2016, 10, 12, 2

2017, 5, 6, 1

2017, 7, 8, 2

];

Re: Multiple alternate states in single expression

vipingarg23 — Thu, 01 Mar 2018 17:20:50 GMT

Great Sunny...Thank you so much for your help....

Re: Multiple alternate states in single expression

sunny_talwar — Thu, 01 Mar 2018 17:42:03 GMT

No problem at all