How to show only last 3 weeks data ?

[#1]

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 ?

[MK_QSL]

Can you upload your sample data file?

[#3]

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

[MK_QSL · Accepted solution]

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)

[jpapador]

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)

[#6]

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

[nirav_bhimani]

Hi,

Using Set analysis also you can do that,

avg({<Date={">$(=WeekStart(max(Date),-2))<=$(=Date(max(Date)))"}>}Availaibility)

Regards,

Nirav Bhimani