Just wondering if anyone can help me with creating a formula and sorting out duplicated values. I created a thread yesterday but in my reply i couldn't find how to attach a Qlikview file so i hope it is ok me starting this new discussion.
Below is what is in the Exel file and in the attachment is one part that i want done in Qlikview. It is a bar chart showing the sum of the Cost and Cost_per_KG for each account when i click on the account number that is the first thing i want which is correct.
The first problem it is a huge Excel file i will be using and i only want Rent of 25 and Supplemented Rent of 10 to be used once for each account. But it would take too long in Excel to do this so i just used it on every line and hoping their is some way i can make it a distinct one value once for each account instead of multipe figures for each account.
What i am really looking for is to make a calculation for when the Cost is under €15 and for when the Cost is higher than €15 and less than €25.
What i would like is when the cost is greater than €15 than just Rent-Cost e.g. i click on account 456789 it's greater than 15 so simply Rent-Cost.
However when the Cost is Below €15 i want the account to get the supplemented rent of €10 so the calculation is Rent-Cost+Supplemented Rent.
The file i will be using has many accounts so i was wondering is their some sort of chart or table that i can use a formula to notice when the figure is below €15 and the Supplemented rent is automatically added. So when i click on an account it recognizes whether it's under or over €15 and makes the calculation.
I am very new to Qlikview and i am not sure if this possible but if it is it would be a huge help for me so if anyone could help it would be much appreciated.
King Regards.
| Account | Item | Cost | Cost_per_KG | Rent | Supplemented_Rent |
| 456789 | Crisps | € 3 | 0.25 | 25 | 10 |
| 456789 | Chocolate | € 5.00 | 0.4 | 25 | 10 |
| 456789 | Tea | € 6.00 | 1.5 | 25 | 10 |
| 456789 | Coffee | € 4 | 3.6 | 25 | 10 |
| 456786 | Crisps | € 1 | 0.25 | 25 | 10 |
| 456786 | Chocolate | € 3.00 | 0.4 | 25 | 10 |
| 456786 | Tea | € 4.00 | 0.45 | 25 | 10 |
| 456786 | Coffee | € 2 | 0.5 | 25 | 10 |
| 456786 | Crisps | € 1 | 0.25 | 25 | 10 |
| 456775 | Chocolate | € 1.20 | 2.5 | 25 | 10 |
| 456775 | Tea | € 1.50 | 0.45 | 25 | 10 |
| 456775 | Coffee | € 2 | 0.5 | 25 | 10 |
| 456775 | Crisps | € 1 | 0.25 | 25 | 10 |
| 456771 | Chocolate | € 5.00 | 1.5 | 25 | 10 |
| 456771 | Tea | € 3.00 | 2.3 | 25 | 10 |
| 456771 | Coffee | € 2 | 2.36 | 25 | 10 |
| 456771 | Crisps | € 3 | 0.25 | 25 | 10 |
| 456771 | Chocolate | € 2.00 | 0.4 | 25 | 10 |
| 456771 | Tea | € 2.78 | 0.45 | 25 | 10 |
| 456769 | Coffee | € 3 | 0.5 | 25 | 10 |
| 456769 | Crisps | € 4 | 0.5 | 25 | 10 |
| 456769 | Chocolate | € 1.20 | 0.4 | 25 | 10 |
| 456769 | Tea | € 1.87 | 0.45 | 25 | 10 |
| 456769 | Coffee | € 2 | 0.5 | 25 | 10 |
