Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

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.

last week is not finished.jpg

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

1 Solution

Accepted Solutions
Highlighted
flipside
Valued Contributor II

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

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 )

7 Replies
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand
flipside
Valued Contributor II

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

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

Highlighted
flipside
Valued Contributor II

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

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 )

Not applicable

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

Thanks Gysbert - Here is a QVW example

Not applicable

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

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

MVP & Luminary
MVP & Luminary

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

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

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

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/