Drop last data point as the 'count' is incomplete?

[#1]

Hi all,

I want to remove the last data point from the graph below as is incomplete data (ie the week is incomplete until Saturday) and so skews the average.

Dim = JOB_DATE_A_FINISH_WEEK

Dim format = 201501 ...201552 (Year/Week)

expression  =count({< Job_Date_Completed_Year = {'2015'}>} JobCounter  )

Tried 'Max'-1  & 'Maxstring'-1 to limit the set, but without success to date and dimension limits doesn't seem to be the answer either.

Hope this is relatively easy - over to you guys...

J

[Gysbert_Wassenaar]

Can you post a small qlikview document that demonstrates the problem?

[flipside]

Are you using a Master Calendar? If so, add an additional field such as ...

if (TempDate>=MonthStart(today()),1,0) as CurrMonthFlag

then in your expression include set analysis similar to ...

Count( {$<CurrMonthFlag = {0}>} VALUE)

This example is based on a month dimension but should give you the idea.

flipside

[flipside · Accepted solution]

If not using a Calendar, seeing as you are using an incremental week index, you can also try this ...

sum({<JOB_DATE_A_FINISH_WEEK={'<$(#=Max(JOB_DATE_A_FINISH_WEEK))'}>} JobCounter )

[#5]

Thanks Gysbert - Here is a QVW example

[#6]

Yes, as I'm not using a std cal this won't work but I understand the principle would work.

[Gysbert_Wassenaar]

See attached qvw.

[#8]

That's an elegant solution - but it appears that I gave you a false impression that there would be 7 'jobs' per week.

-  not so I'm afraid. The number of jobs is variable.

The issue I is rooted in the 'Max' week number so the response by 'flipside' was the correct answer once I got my head around it

Thanks both for your help/