Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I´m looking for a solution on how to divde an amount on 5 categories.
For instance, I have 2 tables that should be connected to eachothers.
One of the table includes the Companyname along with salesamount account and i.e., the other table include categories, account and percentage. Both tables have the Companynumber as the uniqe key.
What I would like to do in my example is that if I have the amount 10 000 and I have 4 000 on the account 500. I would first like the Qlikview app to find the sales amount 4000 in the table where the account is 500. Then I would like to place it as one category (because the category for instance materials is named where the account is 500). Afterwards I would like to take the remaining sales amount 6000 and divide the rest according to the percentage. In the categories we have for instance 50/50 on two other categories.
Do anyone have an solution on how to in Qlik write the algoritim so that the script could divde the amount in this way?
This is the ex. tables
Table 1 | |||
Companynumber | Name of Company | Account | Sales amount |
11 | A | 500 | 4 000 |
11 | A | …. | 3 000 |
11 | A | …. | 1 500 |
11 | A | …. | 1 500 |
Table 2 | ||||
Companynumber | Account | Percentage | Category 1 | Category 2 |
11 | 500 | 1 | materials | iron |
11 | 0,5 | … | … | |
11 | 0,06 | … | … | |
11 | 0,4 | … | … | |
11 | 0,02 | … | … | |
11 | 0,02 | … | … |
The result i would to achieve is that the categories should be calculated as the follow
Category result | |||||
Companynumber | Account | Percentage | Category 1 | Category 2 | Sales amount |
11 | 500 | materials | iron | 4 000 | |
11 | 0,5 | … | … | 3 000 | |
11 | 0,06 | … | … | 360 | |
11 | 0,4 | … | … | 2 400 | |
11 | 0,02 | … | … | 120 | |
11 | 0,02 | … | … | 120 |
The third table show the correct values on the diffrent categories (... = different categorie names)
The algoritm should be; first look in the table category, are there any account that you find in table 1. Then show that amount on that category. Afterwards it look if there are any percentage and then take the total amount on Company a, but first reduce the amount with the account amount and the divide that amount according to the percentage.
Hope this explains it:)