Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, hope someone can help with this.
I'm creating a profit and loss Qlik Sense app and want to include percentages as a measure within a straight table. The percentage is calculated as the sum of values for the particular line divided by the sum of values for another line. E.g. The following is how I want the table to be, with the Percent column being a measure.
LineDescription | Value | Percent | i.e. |
Sales | 200 | 100% | 200/200 |
Cost of Sales | 70 | 35% | 70/200 |
Margin | 130 | 65% | 130/200 |
For each line I know via a dimension that the row to divide by is 'Sales', e.g. PercentReference in the following
Load * Inline [
LineDescription, Value, PercentReference
Sales,200,Sales
Cost of Sales,70,Sales
Margin,130,Sales
];
I want to add a measure for the % along the lines of
Sum(Value) / Sum({$<LineDescription = {Sales}>}Value)
i.e. the row value divided by the value where the LineDescription is Sales.
Is it possible to do this, perhaps I haven't got the syntax right?
Thanks in advance.
sum(Value) / Sum(TOTAL{$<LineDescription = {Sales}>}Value)
Thank you for your reply Nitin. Sorry, I didn't make my request clear, I should have said that rather than fixing the value of {Sales} I want this to be whatever PercentReference is for the row,
e.g.
Sum(Value) / Sum({$<LineDescription = {whatever the value for PercentReference }>}Value)
where in my example PercentReference has a value of Sales but there could be many other rows which have different values for PercentReference.