Calculation based on particular cell in table

[Anonymous]

See attached spreadsheet.

In a Qlikview table I want to be able to distribute the commission made by the store manager among the rest of the employees based on the % of sales they had (excluding Manager's sale). The manager's job code is MGR in this example.

Is there a way to do that in Qlikview?

[sergio0592]

Hi,

It's a little fuzzy, can you post the expected output  in the column N?

[Anonymous]

I've updated the column N with the expected results. Please have a look.

Thanks for your help.

[sergio0592]

Hi,

It works with the following formula. But for the field % Merch Sales, you have to replace , by .

[Anonymous]

Thanks - I am not sure I quite understand what you mean by replacing the % Merch Sales field. Can you elaborate a bit more?

[sergio0592]

Try the formula with your data.

It's just that i made a sample with your data and i put as % Merch Sales field with . instead ,. eg 18.90% instead 18,90%. But if % Merch Sales is in you app the result of a calculation, it should be ok.

[Anonymous]

% Merch Sales is a calculation but I used relative to get the %.

Is there any other way to get the decimal value for % Merch Sales as this is the issue am facing now?

[sergio0592]

So,

-where % Merch Sales is calculated? In your load statement or in your chart?

-can you send the formula that allows to get % Merch Sales?

[Anonymous]

% Merch sales is calculated in the table (not load statement).

The formula is:

sum({<JobCode = {'MGR'}>}Sales) and I use the "Relative" function to give me percentages.

[sergio0592 · Accepted solution]

Don't use relative option and use the following formula instead :

sum({<JobCode = {'MGR'}>}Sales) / sum(TOTAL Sales)