Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
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

1 Solution

Accepted Solutions
Highlighted
nathanfurby
Valued Contributor

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

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.

View solution in original post

21 Replies
Highlighted
Not applicable

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

reply awaited Sad

Highlighted
nathanfurby
Valued Contributor

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

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.

Highlighted
Luminary
Luminary

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

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

Highlighted
Not applicable

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

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

Highlighted
nathanfurby
Valued Contributor

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

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

Highlighted
nathanfurby
Valued Contributor

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

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.

Highlighted
Not applicable

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

I have attached the application file.

Highlighted
Not applicable

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

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

Highlighted
nathanfurby
Valued Contributor

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

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.