topic Re: How to use set analysis while calling a variable in QlikView

How to use set analysis while calling a variable

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

Hi,

In my Pivot table I am using one calculated dimension and an expression as below:

=Num($(vScore-1) + $(vScore-2), '##.0') <-- My current selections in list box for Month='Feb'

and I am getting the correct results.

Now, I want to add one more expression where it gets data for the month='Jan' like below

=Num({ 1 <Month={'Jan'}>} $(vScore-1)+$(vScore-2), '##.0') <-- This expression is not working and I am getting 0

How can I re wite the above expression ?

Thanks

Re: How to use set analysis while calling a variable

Chanty4u — Sat, 24 Mar 2018 17:56:23 GMT

may be try this

=Num({ 1 <Month={'Jan'}>} '$(vScore-1)'+'$(vScore-2)', '##.0')

=Num({ 1 <Month={'Jan'}>} "=$(vScore-1)"+"=$(vScore-2)", '##.0')

Re: How to use set analysis while calling a variable

sasiparupudi1 — Sat, 24 Mar 2018 17:58:01 GMT

Try

=Num(Only({ 1<Month={'Jan'}>} $(vScore-1)+$(vScore-2)), '##.0')

Re: How to use set analysis while calling a variable

trdandamudi — Sat, 24 Mar 2018 18:11:58 GMT

Not getting any values..

Thanks

Re: How to use set analysis while calling a variable

MK_QSL — Sat, 24 Mar 2018 18:21:49 GMT

What is vScore definition?

Re: How to use set analysis while calling a variable

Chanty4u — Sat, 24 Mar 2018 18:23:06 GMT

try this

=Num({ 1 <$(vScore-1)+$(vScore-2),Month={'Jan'}>} , '##.0')

Re: How to use set analysis while calling a variable

trdandamudi — Sat, 24 Mar 2018 18:55:35 GMT

Manish,

The vScore-1 definition is as below: ( I created them in the variable window)

Score-1 <--- Variable

If($(vHI) >= Ta_In_Hu,10,Num(($(vHI) - Min_HI)/(Ta_In_Hu - Min_HI) * 20,'##')) <--- Expression

vHI <--- variable

Num(Sum({<[HI]={'Y'}>} _SS_Count)/Sum({<[HI]={'Y'},[D-LS-MA]=>} Total<$(=vHchy_Select_Dim)> _SS_Count),'###%') <--- Expression

While the vScore-1 variable is evaluating the value it also looks into the variable "vHI" as mentioned above. Hope this helps..

Thanks

Re: How to use set analysis while calling a variable

MK_QSL — Sat, 24 Mar 2018 19:46:12 GMT

You need to use along side

[HI]={'Y'}

But again question is what is vHchy_Select_Dim?

Re: How to use set analysis while calling a variable

trdandamudi — Sat, 24 Mar 2018 20:30:10 GMT

vHchy_Select_Dim is variable defined as below:

vHchy_Select_Dim <--- Variable

=('['&_Work_Dims&']') <--- Expression ( This is getting the data from an data island table as show below)

LOAD * INLINE [
    _Work_Dims
     Level A View
     Level 1
     Level 2
     Level 3
     Level 4
     Cc_Level 1
     Cc_Level 2
     Cc_Level 3
     Cc_Level 4
     Cc_Level 5

];

With the existing expressions if I select a month in the list box then every month shows the correct values. I want to show those months in one pivot like Jan, Feb Mar etc. ( That is the reason I want to pass the month in the Set

analysis)

Thanks

];

Re: How to use set analysis while calling a variable

stevedark — Sat, 24 Mar 2018 21:53:46 GMT

The issue is that the set analysis needs to go inside the aggregations, and the num function is not an aggregation.

You will need to insert the set analysis into both of the variables (which I presume do have aggregation functions in them).

If you really don't want to do that then this should work, but won't be as efficient as it could be:

=Num(sum({1<Month={'Jan'}>} aggr($(vScore-1)+$(vScore-2), Month), '##.0')

Re: How to use set analysis while calling a variable

trdandamudi — Sat, 24 Mar 2018 22:28:04 GMT

Steve,

I applied the logic from the below line into my code:

=Num(sum({1<Month={'Jan'}>} aggr($(vScore-1)+$(vScore-2), Month), '##.0')

In my dimension I have 11 different values and when I applied above expression it is giving value for only one row and remaining are zeros ? what ever the value it is displaying for one row is correct. am I missing something here ?

Thanks for your time.

Re: How to use set analysis while calling a variable

stevedark — Sat, 24 Mar 2018 22:39:41 GMT

Sorry, I missed out some rather important code.

The AGGR statement allows you to calculate values for each of the dimensions you specify in the paramters and then do a further aggregation (and set analysis) over them. This is why Month was added and the correct answer was returned.

You also need to add all of the other dimensions that you have in your table to the parameter list, e.g.

=Num(sum({1<Month={'Jan'}>} aggr($(vScore-1)+$(vScore-2), Month,Field1,Field2), '##.0')

I suspect you will need to add $(=vHchy_Select_Dim) as one of the fields.

Re: How to use set analysis while calling a variable

trdandamudi — Sat, 24 Mar 2018 23:06:08 GMT

I re did the expression based on the below format:

=Num(sum({1<Month={'Jan'}>} aggr(aggr($(vScore-1)+$(vScore-2), Month,$(vHchy_Select_Dim)),$(vHchy_Select_Dim)) '##.0')

I am getting the results for all the rows but it is not applying the Set analysis for "Month" and the sub total is wrong ?

Thanks

Re: How to use set analysis while calling a variable

stevedark — Sat, 24 Mar 2018 23:48:06 GMT

$(vHchy_Select_Dim) should only appear once.

You also need to include any other fields which are included as dimensions, or does the table only have the one dimension?

Is Month the name of a field in your data model? Does it contain the names of the months, exactly as you are matching them in the set analysis?

Having the set analysis inside the two expressions (rather than outside with an aggr) would definitely be better. It would need to go in your vHI variable by the looks of things. This will require more duplication of variables, but would be ultimately better.

You could look at this article about passing parameters into variables - but this could be overkill for your use case:

https://www.quickintelligence.co.uk/variables-parameters-load-script/

Re: How to use set analysis while calling a variable

trdandamudi — Sun, 25 Mar 2018 12:15:53 GMT

I have only one calculated dimension and yes "Month" is a field in the data model. Looks like I need to add more variables. But one set of variables do not have any aggregation and they all have only Num().

Thanks

Re: How to use set analysis while calling a variable

sasiparupudi1 — Sun, 25 Mar 2018 17:02:17 GMT

Try adding your set analysis to the aggregation

In the inside variable.

vHI <--- variable

Num(Sum({<[HI]={'Y'},Month={'Jan'}>} _SS_Count)/Sum({<[HI]={'Y'},[D-LS-MA]= Month={'Jan'} >} Total<$(=vHchy_Select_Dim)> _SS_Count),'###%') <--- Expression

Re: How to use set analysis while calling a variable

stevedark — Sun, 25 Mar 2018 20:48:19 GMT

This code is missing a comma before Month. Also, care needs to be taken as that variable is used for other periods as well.

Re: How to use set analysis while calling a variable

sunny_talwar — Mon, 26 Mar 2018 02:04:43 GMT

Why don't you share a sample (mocked up or anonymized) to help us see your issue