% of Sales

[Anonymous]

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%

[jonathandienst]

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

[Anonymous]

The sum if statement may not work because the if statement will always return false as the LineNum is always not equal to Denominator.

[Anonymous]

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?

[#5]

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)

)