Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi,
If i understood your Question, attached application may be your soultion.
- Sridhar
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.
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
You can use the Set Analysis that I included in the earlier example. I added a RowsExists column in the load script.
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.
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
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.
Oh - and don't forget to mark the question as answered