First week of the month figures

[Anonymous]

Hi,

I need an solution for the following Case. I think a set analysis statement will do the job.

Every week snapshot of the order backlog is made in the backend. But now I only need the backlog of the figures in the first week of each month (bold)

Which set analysis is correct? Or do I need to create a new field in my Calendar?

Thanks

Rob.

YearMonth	WeekY	Order Backlog EUR
jan 2016	2016/01	8807667
	2016/02	7648008
	2016/03	6966561
	2016/04	7172664
feb 2016	2016/05	7167273
	2016/06	7209022
	2016/07	7359880
	2016/08	7484111
	2016/09	7521138
mrt 2016	2016/10	7654443
	2016/11	7305207

[Gysbert_Wassenaar]

You should make sure your WeekY field is a date: dual(WeekY, MakeWeekDate(left(WeekY,4),right(WeekY,2))) as WeekY. Or create another numeric field that increases for each week. Then you can use FirstSortedValue([Order Backlog EUR], -WeekY) as expression in a chart with YearMonth as dimension.

[Anonymous]

Hi Gysbert,

Thanks for advise but there not another solution?

A flag for example (1,0) that indicates the first Week of the Month? That should be simpeler. And then sum(BACKLOG EUR * Flag) for the calculation. But how do I get the first week of each month?

Regards

RObert

[Gysbert_Wassenaar]

You can add a flag field in the script if you want. You can get the first week of the month with something like If(WeekStart(MyDate)>=MonthStart(MyDate) and MyDate-MonthStart(MyDate)<7,Week(MyDate))