Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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?

View solution in original post

17 Replies
ali_hijazi
Partner - Master II
Partner - Master II

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

then in your chart your expression may look something like this:

sum({State1}Sales)

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

I can walk on water when it freezes
mightyqlikers
Creator III
Creator III

hi ,

please share some sample data.

and expected output.

$@

sunny_talwar

Try this with TOTAL qualifier

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

tresesco
MVP
MVP

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

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

Not applicable
Author

This is the sample data and the expected result.

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

Sem título.png

sunny_talwar

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

Not applicable
Author

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?

sunny_talwar

Try this:

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

Not applicable
Author

sunindia 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)