Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In the following straight table, I would like to calculate the % of Sales. Some of the lines are using Net Sales to calculate. But for some product related lines, we need to use the Sales for that particular product to calculate. Below is an example of my data:-
LineNum Account Value Denominator
10 Sales - A 100
20 Sales - B 300
30 Sales - C 600
40 Net Sales 1000
50 COS - A 30 10
60 COS - B 50 20
70 COS - Others 120
What I would like to build is a straight table with dimensions LineNum and Account and 2 expressions. The first expression is Sum(Value) works fine.
The second expression is to calculate % of Sales.
For the accounts with no value in denominator, then % of sales = Sum(Value) / Sum({$<Account={"Net Sales"}>} Total Value). This formula works fine.
For the accounts with values in denominator, then % of sales = Sum(Value) / Sum({$<LineNum=Denominator>} Total Value). But this formula does not work.
Can someone help to specify what I should use for the second expression?
The expected result should be as follows.
LineNum Account Value % of Sales
10 Sales - A 100 10%
20 Sales - B 300 30%
30 Sales - C 600 60%
40 Net Sales 1000 100%
50 COS - A 30 30%
60 COS - B 50 17%
70 COS - Others 120 12%
Hi
The second expression does not work because the set calculation is done once for the chart, not once per dimension. So QV cannot determine at that time the value of Denominator for your set expression. It shoul d work if you change that to a sim(if()) epxression.
This is what I think you need:
If(Len(Denominator) = 0,
Sum(Value) / Sum({<Account={'Net Sales'}>} Total Value),
Sum(Value) / Sum(Total If(LineNum=Denominator, Value))
)
Hope that helps
Jonathan
The sum if statement may not work because the if statement will always return false as the LineNum is always not equal to Denominator.
It does not work because the LineNum is not equal to Denominator. Any other
thoughts? Do we need to use indirect set analysis to do this?
In the above example you are trying to access records from a different row while trying to calculate an expression for a particular row. This is not possible as Jonathan mentioned since SET calculation is done once for the chart.
An alternate way of thinking is cerating/adding a new column to the existing table using Resident Table.
Something Like:
LOAD
LineNum as Denominator,
Value as Value2
Resident Table1
Where Not Exists (Denominator);
The in the Expression:
If(Len(Text(Denominator))>0,
Sum(Value) / Sum(Value2),
Sum(Value) / Sum({<Account={'Net Sales'}>} Total Value)
)