Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I've attached my application that I'm having issues with, I want to perform a percent calculation that uses the first expression (Volume) and divide by the total volume - which is the value I currently have as a calculated dimension.
However, syntax is getting the better of me and I'm struggling to get the same value to apply across all the 'band names' (the dimension used in the pivot table at the top).
I'm hoping someone can help me, an example would be the first row; cust code = YVBFU105, the total volume is 1,958.40 and I'd like that value to be displayed for each band name in my second expression. Then what I'll do is divide the value from the band name by the total volume (1,958.40) to give me my percentage. But my expression only seems to put the total in one of the band name values - not all of them.
Your help is always greatly appreciated!! Many thanks in advance.
Kind Regards,
Dayna
Hi Dayna,
Try using the "NODISTINCT" qualifier for your aggr function as shown below;
I believe that get's you your desired result.
Let me know if that helps!
Thanks,
Camile
This?
=num(sum(Aggr(sum(aggr(sum({$<[AR - BandName] = {'Current'}>}[AR - total]/[AR - Ex Rate]),[AR - Cust Bill])),[AR - BandName]))
+
sum(aggr(aggr(sum({$<[AR - BandName] = {'1-30 Days', '31-60 Days', '61-90 Days', '91 Days - Year', 'Year +'} >} [AR - total]/[AR - Ex Rate]),[AR - Cust Bill])
,[AR - BandName]))
,'#,##0.00')
Hi Dayna,
Try using the "NODISTINCT" qualifier for your aggr function as shown below;
I believe that get's you your desired result.
Let me know if that helps!
Thanks,
Camile
Camile,
That's perfect, thank you! How does the NODISTINCT work in this example? Is it something on the lines of that the aggregation function will ignore the different values (i.e. band name) that it would normally be segregated by?
Kind Regards,
Dayna
If omitted, By default the aggr function uses DISTINCT as its qualifier. If we look at the definition of the aggr function we see the following:
aggr ( [distinct | nodistinct ] [set_expression] expression {, dimension} )
If the expression argument is preceded by the nodistinct qualifier,
each combination of dimension values may generate more than one
return value, depending on underlying data structure. If the expression
argument is preceded by the distinct qualifier or if no qualifier
is used at all, each combination of dimension values will generate
only one return value.
In your application that is exactly what is occurring - the NODISTINCT qualifier allows you to generate more than one return value (only one was being returned before because the qualifier was omitted.
Thanks Camile! Must remember for next time..!