Discussion Board for collaboration related to QlikView App Development.
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
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
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.
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.
Please post the QV document with sample data & comments about your question. I am sure one of us would quickly reply back.
Cheers - DV
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
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.
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
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.