Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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:
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?
The inline script was for example of the solution, use it in your dataset the Hierarchy() function
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
Thank you for your time, maybe this is a better explanation.
Thank you again,
Zeno