topic Re: Formula Help!!! in QlikView

Formula Help!!!

Tue, 23 Jun 2015 07:29:04 GMT

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

Re: Formula Help!!!

awhitfield — Tue, 23 Jun 2015 08:35:38 GMT

Hi there,

what should happen if the cost is over 25?

Andy

Re: Formula Help!!!

Tue, 23 Jun 2015 08:45:47 GMT

Hi Andrew,

With the figures i would be using their would be no figure bigger than €25.

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.

Re: Formula Help!!!

awhitfield — Tue, 23 Jun 2015 09:32:32 GMT

Hi, try this:

If(Sum(Cost)>= 15, Rent-(Sum(Cost)),(Rent-(Sum(Cost))-[Supplemented_Rent]))

Andy

Re: Formula Help!!!

Tue, 23 Jun 2015 09:51:15 GMT

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.

Re: Formula Help!!!

sasiparupudi1 — Tue, 23 Jun 2015 10:01:02 GMT

Is 25 for Rent and 10 for Supplemented_Rent constant across all the accounts?

Re: Formula Help!!!

awhitfield — Tue, 23 Jun 2015 10:15:49 GMT

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

Re: Formula Help!!!

Tue, 23 Jun 2015 10:57:13 GMT

Andrew that was a huge help it really helped and the calculations make things so much easier and quicker than Excel.

Thank you.

Re: Formula Help!!!

awhitfield — Tue, 23 Jun 2015 11:07:52 GMT

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

Re: Formula Help!!!

Tue, 23 Jun 2015 11:13:15 GMT

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.

Re: Formula Help!!!

awhitfield — Tue, 23 Jun 2015 11:25:29 GMT

Happy Qliking

Andy