Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Expression added to Bar chart and Straight table in the attached, I've have also added a calculation condition, so the charts only work when you select an account from the list box
Andy
Hi there,
what should happen if the cost is over 25?
Andy
Hi Andrew,
I am not sure to Edit i just noticed i made an error in my first post what i meant to say if the figure is under 15 than i want to take €10 from the total and not add €10,
For example on Account 456786 Account the Cost is €11 so what it should calculate as
Rent €25 - Cost €11 = Total €14-€10 Supplemented rent = €4
Sorry if this is confusing i am not sure if it's even possible to do what i am trying to do.
Hi, try this:
If(Sum(Cost)>= 15, Rent-(Sum(Cost)),(Rent-(Sum(Cost))-[Supplemented_Rent]))
Andy
Hi Andrew
Thanks again Andrew what i am getting is when i put in the formula it says Allocated Memory exceeded like i said it's a massive file so maybe i can't do this or i am doing something wrong.
Is their any chance you or even anyone could put that formula into the Qlikview file that i attached as maybe i am doing something wrong like i said i am very new to Qlikview so i could be doing something obvious wrong.
I would only be looking at one account at a time by clicking on it so maybe an Bar Chart is not the best maybe some type of table i am not sure really what's the best way to present the data.
Again thanks for taking the time to reply the formula you advised looks correct but i am probably just making some simple error.
Thanks again.
Is 25 for Rent and 10 for Supplemented_Rent constant across all the accounts?
Expression added to Bar chart and Straight table in the attached, I've have also added a calculation condition, so the charts only work when you select an account from the list box
Andy
Andrew that was a huge help it really helped and the calculations make things so much easier and quicker than Excel.
Thank you.
Happy to help! If the replies answers you query, please mark the response as CORRECT in ACTION on the original thread, It saves members looking at resolved issues
Thanks in advance
Andy
Thats done now Andy again thanks for the help i am sure you will see many more questions from me. The software is fantastic but overwhelming at first but hoping to learn more in the next few weeks.