Calculate percentage based on a dynamic field value

[zenobendi]

Hi everyone, I need some help regarding a percentage calculation in qlik sense. In particular, imagine that I have a table structured in this way and that I want to calculate the percentages on the basis of the "Base code".

So what I'm trying to do is a formula like:

sum(BIL_Val_CAD)/SUM(total(IF([Account Code]=[Base Code], BIL_Val_CAD)) but it doesn't work

Thanks

[zenobendi]

So just to be more clear, I have this table:

And I need to calculate not the percentage just on sales but a percentage that is based on the base code.

Thanks

[kKoumoundouros]

Hello,

You can try with Hierarchy function on load script and then in table you have to calculate for each level the percentage, try below script:

Data:
Load * inline [
Account, Account Code, Base Code, Sales
Sales Canada, 10, 30, 4500
Sales USA, 20, 30, 2500
Sales, 30, 30
Cogs Canada, 40, 10, 1500
Cogs USA, 50, 20, 500
Cogs, 60, 30
];

Sales:
Hierarchy([Account Code], [Base Code], [Account], , [Account]) Load * Resident Data;

Drop Table Data;

Then create a table as below:

[zenobendi]

Thanks for your kind reply.

Since there are many many accounts and each one has its own base code I'm trying to achieve this without having the need to use an inline function in the script. For example I've tried to put in the formula:

 sum(BIL_Val_CAD)/SUM(total(IF([Account Code]='30', BIL_Val_CAD))

and it makes the percentage correctly on the account code=30. Is there a way to achieve this but dinamically so without putting the exact value but the field value (Base code) that changes accordingly to the account code?

[kKoumoundouros]

The inline script was for example of the solution, use it in your dataset the Hierarchy() function

[zenobendi]

Thank you. I'm trying to put the Hierarchy function in the script as follow but when I put it the load seems to hang.

Is there a way to achieve this goal directly through the expression? Thanks

[zenobendi]

Thank you for your time, maybe this is a better explanation.

In particular I have a structured table like the one below where the second field, "BILS_Base_Code" is always equivalent to a specific value of the "BILS_Liv_Cod" field and I would need to calculate a percentage that shows me the value of the BILS_Liv_Cod by comparing it with the value of the corresponding BILS_Base_Code (since the real table is really big: 1000+ rows). Please also note that in the real table the field "BILS_Base_Code" is filled in each row.

Just to make an example, where the BILS_Liv_Cod=80, I need a formula that shows -3.058.072/6.243.119 (so the value where BILS_Liv_Cod=50)

Thank you again, 

Zeno