topic how to make an expression based on previous rows??? help needed in QlikView

how to make an expression based on previous rows??? help needed

Mon, 11 Apr 2011 08:37:21 GMT

Hello all,

I have a scenario, which i am unable to solve in QV. The scenario is given below

on Date D1, we have No of extracted rows 1000, on D2 900, on D3, 950, On D4 100. Now let suppose on D5 the extracted rows is 100, which is much less than the standard deviation or you can say the average of previous days, then i want to mark this cell as red. How can I calculate the avarege based on previous days and then compare D5 value with previous days.

To clerify it, I am making the following sample date

D1 D2 D3 D4 D5

Job1 1000 900 950 100 100

Job2

Job3

Now in the abve table, the I want to compare every date with previous dates average and if the difference is greater than 20%, then i want to mark it red.

Average = (date-4+date-3+date-2+date-1)/4 which in this case is ------> (D1+D2+D3+D4)/4

percentage difference= (todays extracted rows/average)*100

If (percentage difference >20) mark the cell red

Please read this scenario and help me. Let me know if anything is unclear

Arif

how to make an expression based on previous rows??? help needed

Mon, 11 Apr 2011 09:21:15 GMT

reply awaited

how to make an expression based on previous rows??? help needed

nathanfurby — Mon, 11 Apr 2011 10:13:41 GMT

I think you probably want to use the TOTAL keyword in some of your expressions.

I'd post a sample if you are looking for more help.

how to make an expression based on previous rows??? help needed

IAMDV — Mon, 11 Apr 2011 10:16:51 GMT

Please post the QV document with sample data & comments about your question. I am sure one of us would quickly reply back.

Good luck!

Cheers - DV

how to make an expression based on previous rows??? help needed

Mon, 11 Apr 2011 11:17:07 GMT

Okay, below is the pivot table with its results

as you can see in the attached image, I have encircled one cell, 192. As you can see, for all previous dates, the numbers are quite bigger but on this specific date, the number is only 192. so i want to make an expression in the background, where if the number's difference with previous 4 days average is greater than 20%, then i would want this cell to be highlighted as red.

I can post the Qlikview file, if this image does not help. I just want to know how can i make an expression to perform this task

Arif

how to make an expression based on previous rows??? help needed

nathanfurby — Mon, 11 Apr 2011 11:23:58 GMT

Post the QV file - will be much easier to tell you the correct expression.

how to make an expression based on previous rows??? help needed

nathanfurby — Mon, 11 Apr 2011 11:49:07 GMT

If you don't want to post the QV then try an expression like this:

Sum(TOTAL <session_name> Value) / Count(TOTAL <session_name> started_date)

EDIT - changed field names.

how to make an expression based on previous rows??? help needed

Tue, 12 Apr 2011 08:06:37 GMT

I have attached the application file.

how to make an expression based on previous rows??? help needed

Tue, 12 Apr 2011 08:08:00 GMT

here is the qvd file for the above application.

Please check this and tell me what expression should i use to perform the task that i have requested earlier

Arif

how to make an expression based on previous rows??? help needed

nathanfurby — Tue, 12 Apr 2011 09:13:32 GMT

Potential solution attached. Take a look at the second table for examples of expressions you could use. Play around with them in this format to get the number you require if the first table isn't adequate.

how to make an expression based on previous rows??? help needed

Tue, 12 Apr 2011 11:45:45 GMT

Okay, let me first appreciate the level of cooperation that everyone has provided, NathanFurby in specific and everyone else in general

So here is another question. I want every row to be checked against the average of previous rows. The example that you have provided checks it against the overall total and average, while i want to check it against the average of the previous five rows.

I mean for every date, i want to check the no of rows of that date to the average no of rows against the five previous dates. Did you get my question, I can put it another way if you want?

and once again, thanks a lot

Arif

how to make an expression based on previous rows??? help needed

Tue, 12 Apr 2011 12:34:15 GMT

I have tried inserting the above function so that for every date, the average of previous date is calculated..for example....for Date3, the average of date2 and date1 is comared, for date4, the average of date3, date2 and date1 is compared, and for date 5, the average of date4, date3, date2 and date1 is compared. How can i perform this?

Arif

how to make an expression based on previous rows??? help needed

Tue, 12 Apr 2011 12:48:07 GMT

Hi,

If i understood your Question, attached application may be your soultion.

- Sridhar

how to make an expression based on previous rows??? help needed

nathanfurby — Tue, 12 Apr 2011 14:20:01 GMT

Yes - as just mentioned, you want to use the Before function. I've just updated my original example as well.

In the second chart the only difference is that the Above function is used instead.

how to make an expression based on previous rows??? help needed

Tue, 12 Apr 2011 14:39:08 GMT

both of you (Nathan & Sridhar ) this is just excellent. This is exactly what i wanted. Awsome.

Now just a small query...If i want to count only those previous rows, where the (no of rows) field is not null or not zero...then how can i modify the below expression

RangeCount(Above(started_date,1,NoOfRows()))..... no_of_rows is a field in this table.

Arif

how to make an expression based on previous rows??? help needed

nathanfurby — Tue, 12 Apr 2011 14:47:21 GMT

You can use the Set Analysis that I included in the earlier example. I added a RowsExists column in the load script.

how to make an expression based on previous rows??? help needed

nathanfurby — Tue, 12 Apr 2011 15:18:18 GMT

OK here is the modified version again. Had to make a change to the load statment for that RowsExist column I introduced. This is because the RangeCount function does not take a SET statement.

how to make an expression based on previous rows??? help needed

Wed, 13 Apr 2011 08:05:57 GMT

Excellent...I really appreciate your help. This has solved my problem.

I am new to Qlikview so can you please suggest a reading meterial, where i can read in detail about set analysis and complex expressions in Qlikview. I have the reference manual but it does not explain these things in details

Arif

how to make an expression based on previous rows??? help needed

nathanfurby — Wed, 13 Apr 2011 08:45:07 GMT

QlikTech are a bit useless at producing documentation - they like you to pay for their classroom training in order to get it

But you can go here for some good stuff:

http://community.qlik.com/gettingstarted/

Plenty of videos and training to get you going. After that just start exploring the samples that come with the app and the examples posted on the community site and experiment. The built in help file is often quite useful.

how to make an expression based on previous rows??? help needed

nathanfurby — Wed, 13 Apr 2011 09:37:25 GMT

Oh - and don't forget to mark the question as answered