Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am quite new to Qlikview. I have one expression as
=AVG(if(Week(A_Timestamp)=Week(Now())-2,Availaibility))
Problem is if I didn't get feeds for last few weeks, my dimension show empty values. I don't want that to happen and want to show data not in comparison to current date but in comparison to max date value in my database.
Something like
=AVG(if(Week(A_Timestamp)=Max(Week(A_Timestamp))-2,Availaibility))
Can someone help me with correct expression to be used ?
Try something like this...
Under your script...add one more line
WEEK(A_Timestamp) = Week
For Availability of last three weeks
=Avg({<Week = {">=$(=Max(Week(A_Timestamp))-2)"}>}Availability)
As per your question...
=Avg({<Week = {"$(=Max(Week(A_Timestamp))-2)"}>}Availability)
Can you upload your sample data file?
Hi Arpit
then you have to store somewhere MaxDate =max(Date) and use
=AVG(if(Week($(MaxDate))=Max(Week($(MaxDate)))-2,Availaibility))
Best regards
Chris
Try something like this...
Under your script...add one more line
WEEK(A_Timestamp) = Week
For Availability of last three weeks
=Avg({<Week = {">=$(=Max(Week(A_Timestamp))-2)"}>}Availability)
As per your question...
=Avg({<Week = {"$(=Max(Week(A_Timestamp))-2)"}>}Availability)
You could use set analysis instead of an if statement. Example,
Avg({$<Week={$(=Max(A_Timestamp)-3)}>} Availability)
As for the dates with no data. If there is a date entry in the A_Timestamp and just no associated data you, in the script you could do a simple if statement, if(Len(<name of field with null data>) = 0, 0, <name of field with null data>) as newfield
This will create a field with 0's instead of Nulls (not sure if that is what youre looking for tho)
Hi Arpit,
Try something like this.
=Avg({<Date={">=$(=Date(WeekStart(max({$}Date))-14))"}>} Availability)
You can change WeekStart to WeekEnd or just use Max Date and subtract 21 days etc.
if Max Date is 1/5/2014, then WeekStart of 1/5/2014 is 12/30/2013.
If you use Week() then if your data is for Jan or Feb, Max(Week(A_Timestamp))-2 will gibe you -1.
Sean
Hi,
Using Set analysis also you can do that,
avg({<Date={">$(=WeekStart(max(Date),-2))<=$(=Date(max(Date)))"}>}Availaibility)
Regards,
Nirav Bhimani