topic Re: Calculation based on particular cell in table in QlikView

Calculation based on particular cell in table

Anonymous — Mon, 16 Oct 2017 20:49:29 GMT

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?

Re: Calculation based on particular cell in table

sergio0592 — Mon, 16 Oct 2017 21:30:43 GMT

Hi,

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

Re: Calculation based on particular cell in table

Anonymous — Tue, 17 Oct 2017 12:17:19 GMT

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

Thanks for your help.

Re: Calculation based on particular cell in table

sergio0592 — Tue, 17 Oct 2017 12:43:46 GMT

Hi,

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

Re: Calculation based on particular cell in table

Anonymous — Tue, 17 Oct 2017 14:38:25 GMT

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

Re: Calculation based on particular cell in table

sergio0592 — Tue, 17 Oct 2017 14:49:35 GMT

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.

Re: Calculation based on particular cell in table

Anonymous — Tue, 17 Oct 2017 16:27:00 GMT

% 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?

Re: Calculation based on particular cell in table

sergio0592 — Tue, 17 Oct 2017 18:52:39 GMT

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?

Re: Calculation based on particular cell in table

Anonymous — Tue, 17 Oct 2017 20:46:04 GMT

% 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.

Re: Calculation based on particular cell in table

sergio0592 — Tue, 17 Oct 2017 20:59:47 GMT

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

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

Re: Calculation based on particular cell in table

Anonymous — Wed, 18 Oct 2017 12:38:08 GMT

Thanks that's working for me now