topic Re: How to show only last 3 weeks data ? in QlikView

How to show only last 3 weeks data ?

Mon, 06 Jan 2014 15:43:24 GMT

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 ?

Re: How to show only last 3 weeks data ?

MK_QSL — Mon, 06 Jan 2014 15:58:01 GMT

Can you upload your sample data file?

Re: How to show only last 3 weeks data ?

Mon, 06 Jan 2014 16:01:38 GMT

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

Re: How to show only last 3 weeks data ?

MK_QSL — Mon, 06 Jan 2014 16:03:01 GMT

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)

Re: How to show only last 3 weeks data ?

jpapador — Mon, 06 Jan 2014 16:07:05 GMT

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)

Re: How to show only last 3 weeks data ?

Mon, 06 Jan 2014 16:19:26 GMT

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

Re: How to show only last 3 weeks data ?

nirav_bhimani — Mon, 06 Jan 2014 16:26:51 GMT

Hi,

Using Set analysis also you can do that,

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

Regards,

Nirav Bhimani