Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Elsvanhaaren
New Contributor

Get a percentage per subcode per day

Hi Qlik Community,

This forum helped me out with quite a lot of questions already. I found numerous times someone else in the world facing the same challenge as I did and then another one who knew how to handle the challenge. However, this time my question is quite specific and I've been trying to look for an existing question & answer but I couldn't find one.

Hope someone can help me out since I'm stuck

So here is my problem:

I have 2 tables, one knowledge table with codes, subcodes and a percentage of how much of the code is the subcode. This is a given.

Code / Subcode / %

1a50%
1b20%
1c30%
2b10%
2c90%
3a40%
3c60%

 

So multiple subcodes can be within one code and the subcodes also occur in different codes. a in code 1 is the same as a in code 3.

I also have my results per code, per day:

Datum / Code / Amount

day 11500
day 12400
day 13900
day 21300
day 22300
day 23600

 

My desired outcome is:

Datum / Subcode / Amount / Total amount (per day) / Percentage subcode

day 1a610180033,9%
day 1b14018007,8%
day 1c1050180058,3%
day 2a390120032,5%
day 2b9012007,5%
day 2c720120060,0%

 

So all percentages add up to 100% per day.

I tried things like...

(avg(percentage)*sum(amount)) / sum(total <Datum> amount)

or

(sum(percentage)*sum(amount))/sum(total <Datum> amount)

but this doesn't work.

Hope someone can help me, any response is very much appreciated!

 

I added the example in excel

Labels (4)
3 Replies
Partner
Partner

Re: Get a percentage per subcode per day

You are almost there use below expressions

Sum per day - Sum(Total<Datum>Amount)

Percentage  Sub Code per day - Sum(Amount)/Sum(Total<Datum>Amount)

 

result

Capture.PNG

Re: Get a percentage per subcode per day

Try this

 

=Sum(Amount*%)/Sum(TOTAL <Datum> Amount*%)

 

Highlighted
Partner
Partner

Re: Get a percentage per subcode per day

Hi

If you add the two tables Table1 (Code, SubCode, %) and Table2 (Datum, Code, Amount) and link them by code you can create a table in Qlik Sense that looks like this as show in the screen shot below

 

percentage per subcode per day 1.jpgThe formula for Sum Amount is  =Sum)Amount*%)

The formula for Total Amount Per Day is Sum(TOTAL <Datum> Amount*%)

The Formula for Percentage is =Sum(Amount*%)/Sum(TOTAL <Datum> Amount*%)

This results will show the same information as in you excel sheet.

Regards

Frank