Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

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?

17 Replies
ali_hijazi
Honored Contributor

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

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)

mightyqlikers
Contributor III

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

hi ,

please share some sample data.

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)

MVP
MVP

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)

Not applicable

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.

Sem título.png

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?

Not applicable

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)

Not applicable

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

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)

Community Browser