17 Replies Latest reply: Mar 21, 2016 1:27 PM by Saran De

# Set analisys - hot to get a value from a another menber off dimension

Hey,

I have a expression that needs an aggregated values that is in another member of the same dimension.

Something like to compare sum(sales) of vendor A with B, to calculate a percentage.

I have tried set analysis like the follow and didn't worked

Sum( {1<Vendor = {"B"}>} sales)

Thanks!

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

you need to calculate the percentage of what?

Sales of Vendor A to Sales of Vendor B?

Sum({<Vendor={'A'}>}Sales) / Sum({<Vendor={'B'}>}Sales)

alternatively you can create alternate states in Settings->Document Propertie->General Alternate States

you create two list boxes but one give it the alternate state 1 and the other the alternate state 2

sum({State1}Sales)

and the 2nd expression will be something like sum({State2}Sales)

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

hi ,

and expected output.

\$@

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

Try this with TOTAL qualifier

Sum(TOTAL {<Vendor = {'B'}>} sales)

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

If it's about disregarding dimension value, try like:

Sum( total {1<Vendor = {"B"}>} sales)

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

This is the sample data and the expected result.

Thanks for the help, but until now nothing has worked as i need.

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

What are your expressions for sum value and value of vendor a?

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

value of vendor a  = sum(  {\$<vendor={'a'}>}  value)

% sales a / this vendor =sum(  {\$<vendor={'a'}>}  value)/ sum(value)

Sunny T &lt;span class=&quot;icon-status-icon icon-mvp&quot; title=&quot;Mvp&quot;&gt;&lt;/span&gt; escreveu:

What are your expressions for sum value and value of vendor a?

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

Try this:

=Sum(TOTAL <\$(=GetCurrentField([Group Name]))> {\$<vendor={'a'}>} value)/Sum(value)

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

stalwar1 It did not work.

I changed the expression because Qlikview was showing error in expression. But it did not work.

Sum({\$<vendor={'a'}>}  TOTAL <\$(=GetCurrentField([Group Name]))> value)/Sum(value)

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

The expression might be showing error, but did you try clicking okay to check if you see any result?

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

No, i didn't see.

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

The expression with TOTAL qualifier should work.

If Mes is a field in your data model, try

=Sum({\$<vendor={'a'}>}  TOTAL<Mes> value)

in your chart as shown above.

If this works, you need to make the expression also work for changing the dimension group.

You can either add all your dimension fields of the dimension group to the field list, assuming another dimension group field Ano:

=Sum({\$<vendor={'a'}>}  TOTAL<Ano, Mes> value)

or retrieve the currently used dimension field using GetCurrentField(), just like Sunny suggested. You need to replace [Group Name] with the name of the dimension group, of course.

Test the dollar sign expansion using

=Sum({\$<vendor={'a'}>}  TOTAL<\$(=GetCurrentField([Group Name]))> value)

in your chart, but leaving the expression label empty (and again, make sure to use the correct dimension group name).

Now the chart expression header should show the expanded expression. Does the TOTAL qualifier shows the currently used dimension field in its list?

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

The first one works, the other one didn't work.

I will do another test, with my real data.

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

Could you describe your drill down group a bit more detailed?

What's the name of the group? Which fields are in there?

Which expression are you finally using?

And with which dimensions drilled into does the expression work in with which doesn't?

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

Hi Sunny & Stefan,

Can you please tell me if the both Set Analysis are equivalent?

= Sum(TOTAL <\$(=GetCurrentField([Group Name]))> {\$<vendor={'a'}>} value)

=Sum({\$<vendor={'a'}>}  TOTAL<\$(=GetCurrentField([Group Name]))> value)

If so, is the set expression and modifier interchangeable? Sorry, about this basic question. I normally use the second type in my Apps.

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

= Sum(TOTAL <\$(=GetCurrentField([Group Name]))> {\$<vendor={'a'}>}value)

The portion is red is not set analysis, it is TOTAL qualifier with the field name in the form of dollar sign expansion.

The portion in pink is set analysis

AFAIK, TOTAL and set analysis are interchangeable.

• ###### Re: Set analisys - hot to get a value from a another menber off dimension

Yes. I realized now. Thanks Sunny ..