Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

21 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Hi,

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

- Sridhar

nathanfurby
Specialist
Specialist

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.

Not applicable
Author

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

nathanfurby
Specialist
Specialist

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

nathanfurby
Specialist
Specialist

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.

Not applicable
Author

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

nathanfurby
Specialist
Specialist

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

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.

nathanfurby
Specialist
Specialist

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