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

[#1]

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

[#2]

reply awaited

[nathanfurby]

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.

[IAMDV]

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

[#5]

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

[nathanfurby]

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

[nathanfurby]

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.

[#8]

I have attached the application file.

[#9]

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

[nathanfurby]

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.