topic Re: Calculating Difference Between Years in QlikView

Calculating Difference Between Years

Thu, 16 May 2013 14:45:12 GMT

I have an excel table uploaded into qlikview that shows the following

Item 1	Year	Value
Product 1	2010	£1.20
Product 1	2011	£1.00
Product 2	2010	£1.40
Product 2	2011	£0.75
Product 3	2010	£1.40
Product 3	2011	£2.00

I can upload this into Qlikview and produce a straight table using the year and product as dimensions and sum of value in expression.

How can I calculate and show the difference between the years for each product without changing the way the table is loaded from excel?

Re: Calculating Difference Between Years

rwunderlich — Thu, 16 May 2013 15:06:20 GMT

Take a look at the above() function in the Help.

-Rob

Re: Calculating Difference Between Years

devans_1 — Thu, 16 May 2013 15:31:20 GMT

Could this be done in the script ?

one:

Load Excel File

two:

JOIN (one) Load

if (recno () = 1 or Product <> peek (Product), 0, Value - previous (Value)) As ValueDiff,

Product,

Year

RESIDENT one

ORDER BY Product, Year;

Re: Calculating Difference Between Years

stevelord — Tue, 25 Jun 2013 21:30:20 GMT

Hi David, I tried the formula myself because I have a similar need, but QV gives an error message about being out of virtual memory and crashes. Formula looked nice. (For me it is about the 8th or 9th resident load following a variety of other formula loads applied to the third or fourth layer of fields created in the resident loads.. so maybe QV got sucked into a blackhole trying to peek() and previous() through that stuff.)

Hi Thomas, worst case scenario is you can put that on a pivot table, then drag the Year column from the vertical to the horizontal axis. Then when you export to excel you can just do =e2-d2 in column F and copy it down for the difference. (This is my present workaround.)

QlikView staff, all of the set analysis tutorials and posts I've read go as far as helping people get the two sets setup separately (by years, regions, whatever) then stop. I've been wandering through set analysis and alternate states up to my eyeballs, but not seen something where I can make a column to compute the difference between state 1 and state 2. It would be nice if Qlikview had an automated something similar to totals, sums, avg, and such that would give differences say:

=if(Value<>Null() AND Year=2012,Value) - if(Value<>Null() AND Year=2011,Value)

(To use Thomas' data)

Re: Calculating Difference Between Years

Wed, 26 Jun 2013 00:09:51 GMT

I would write the script similar to David, except without the join. Create a whole new table and then drop the first. This will be less likely to give an out of memory exception. I imagine that the JOIN to itself may result in many records.

Re: Calculating Difference Between Years

stevelord — Wed, 26 Jun 2013 13:58:17 GMT

Thanks. Here is what I got to work in my own script but it has an imperfect match likely due to my data structure. I will probably go with order by UserId, TestDate and experiment with ascending/descending to hopefully make it pick the most current test date with a value in the given year. (Sometimes folks get some tests one day and others another time in the year, so we get nulls for some tests on some dates in a given year.)

Difference:

Load *,

if (recno () = 1 or UserId <> peek (UserId), Null(), nrbBMI - previous (nrbBMI)) As cnrbBMI

RESIDENT RiskBanding2

ORDER BY UserId, TestYear;

Drop Table RiskBanding2;

QA Result on my data:

Checking one client's 2010/2011 records "manually" (exported the list of values, filtered to show only folks with values in both years and entered =f-e in Excel), I found 6828 people with BMI data in both years. 140 of these people were not captured by the above piece of script or gave nulls where I had data. Additionally, the script captured 10 people who only had a record in one of the two years (or maybe two records in that year given the script logic). Lastly 3 records gave an up, down, or no change that differed from what I had on my manual process. For all of that it is better than a 97% match to my manual results that I can probably improve with some logical finetuning. (99.8% tolerance over here that will probably only get tighter over time. ) I'm not the one who can give correct answer credit, but maybe the original posting person can try the solutions on his data. My structure isn't the cleanest and I've got 8 resident loads full of formulas built to restructure and populate missing data and/or filter garbage from millions of records on one table linked to a few others.

Re: Calculating Difference Between Years

devans_1 — Tue, 23 Jul 2013 08:49:35 GMT

As an addendum to this, yes, I was wrong to say you should use a JOIN. Not sure why I said this - bit of a slip!. As Michael says, the rest of the solution should work (as I've used it myself many times). You just create a new table with the old fields plus the new one then drop the old table.