Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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?
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)
hi ,
please share some sample data.
and expected output.
$@
Try this with TOTAL qualifier
Sum(TOTAL {<Vendor = {'B'}>} sales)
If it's about disregarding dimension value, try like:
Sum( total {1<Vendor = {"B"}>} sales)
This is the sample data and the expected result.
Thanks for the help, but until now nothing has worked as i need.
What are your expressions for sum value and value of vendor a?
value of vendor a = sum( {$<vendor={'a'}>} value)
% sales a / this vendor =sum( {$<vendor={'a'}>} value)/ sum(value)
Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> escreveu:
What are your expressions for sum value and value of vendor a?
Try this:
=Sum(TOTAL <$(=GetCurrentField([Group Name]))> {$<vendor={'a'}>} value)/Sum(value)
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)