topic Re: Set analysis prob in QlikView

Set analysis prob

nikhilgarg — Wed, 06 Jan 2016 06:45:46 GMT

Hey,

I want to use 2 different states in my expression. My scenario is:

Year : Inherited State

Month : Inherited State

GLDesc : State1

I have line chart with exp:

a: Sum({State1<KPI = {'MktExpse'}>}Value) as Expenses

b: Sum(Value1) as Sales

Now when i select any year or month, Expenses does not change untill i select any value from GLDesc.

I want Expenses expression to change for Year and Month Selection as well.

Thanks,

Nikhil

Re: Set analysis prob

PrashantSangle — Wed, 06 Jan 2016 06:50:18 GMT

Hi,

try without using State1 in your expression.

Regards

Re: Set analysis prob

PradeepReddy — Wed, 06 Jan 2016 06:55:51 GMT

try something like this...

Sum({State1<KPI = {'MktExpse'},Year=$::Year,Month=$::Month>}Value)

Re: Set analysis prob

nikhilgarg — Wed, 06 Jan 2016 06:55:55 GMT

Hey,

If i dont use State1 then i will not be able to bifurcate expenses GLDesc wise. I have to use State1.

Can i use another state as well in same expression for Year and Month?

Re: Set analysis prob

nikhilgarg — Wed, 06 Jan 2016 07:11:46 GMT

Hey,

This is also not working

On 06-Jan-2016 12:26 PM, "pradeep taniparthy" <qcwebmaster@qlikview.com>

Re: Set analysis prob

Kushal_Chawda — Wed, 06 Jan 2016 07:16:21 GMT

try this

Sum({State1*$<KPI = {'MktExpse'},Year=$::Year,Month=$::Month>}Value)

Re: Set analysis prob

MayilVahanan — Wed, 06 Jan 2016 07:18:49 GMT

Try like this

Sum({State1<KPI = {'MktExpse'},Year=$::Year,Month=$::Month>}Value)

Edit: Can you able to post the sample.. The above expression works fine for me.

Re: Set analysis prob

Wed, 06 Jan 2016 07:26:04 GMT

Hi Nikhil,

This approach should give you what you looking for

sum({State1<KPI={'Exp'},Year={$(=concat(DISTINCT chr(39) & Year & chr(39),',') )},Month={$(=concat(DISTINCT chr(39) & Month & chr(39),',') )}>}Value)

Re: Set analysis prob

Wed, 06 Jan 2016 07:28:19 GMT

MayilVahanan‌

Please talk me through your approach. I have never seen this before and this is quite a brilliant way to achieve the desired output.

Thanks,

Byron

Re: Set analysis prob

MayilVahanan — Wed, 06 Jan 2016 07:43:09 GMT

Hi Bryon,

This is the general syntax for include the default state in our expression.

Please refer the attached document.. Or I will upload the sample qv document soon.

Re: Set analysis prob

nikhilgarg — Wed, 06 Jan 2016 08:59:50 GMT

Hey,

Thanks, it works but i didnt get how does the expression works with :

State *$ ?

Re: Set analysis prob

Kushal_Chawda — Wed, 06 Jan 2016 09:03:27 GMT

Hi Nikhil.

State1*$ will give you the intersection of the record for State1 & Selected values

Re: Set analysis prob

nikhilgarg — Wed, 06 Jan 2016 09:29:20 GMT

Ohk, you mean to say that by State1*$ expression,

If i select 100 in GL : State1

and 2015 in Year : inherited state, it will give me data belonging to the combination of both?

Re: Set analysis prob

Kushal_Chawda — Wed, 06 Jan 2016 10:23:37 GMT

Yes, Correct. There is two way to include the selection from inherit state

1) Either include it in set

Sum({State1<KPI = {'MktExpse'},Year=$::Year>}Value)

2) or use like below

Sum({State1*$<KPI = {'MktExpse'}>}Value)

Re: Set analysis prob

Wed, 06 Jan 2016 12:44:14 GMT

Wouldn't Sum({State1*$<KPI = {'MktExpse'}>}Value) also include selections from fields you don't want?

Using Sum({State1<KPI = {'MktExpse'},Year=$::Year>}Value) I know Year will be included in my search with

any alternate state selections from state 1 but nothing else. So this would be the correct one to use in this instance? Please confirm

Thanks,

Byron

Re: Set analysis prob

MayilVahanan — Thu, 07 Jan 2016 08:45:11 GMT

Please refer some sample for Alternate state

Alternate State Sample for Beginners