Current week

[delbooth]

Hi.

I created a master calendar using Josh Goods creating a master Calendar post.  I have tried using the week (I have called it Colweek) field to specify a week to sum data as below: ie sum the quantity collected for week 24. 

sum({< ColYear=(2018),ColWeek=(24)>} [Quantity Collected])

Unfortunatly it does not work, Also how would I specify it to sum for the current or previous week instead of specifying week n umbers.

Many thanks

del

[Chanty4u]

May be this?

sum({< ColYear={'2018'},ColWeek={'24'}>} [Quantity Collected])

[delbooth]

Thanks very much.  is there a way of instead of specifying 24 I can put current week or previous week

[Chanty4u]

try this

for Max week

sum( {< Week={$(=max(Week))} >} Data )

[Silambarasan1306]

You can try this,

Current week,

sum({< ColYear={"$(=Max(ColYear))"},ColWeek={"$(=Max(ColWeek))"}>} [Quantity Collected])

Previous week,

sum({< ColYear={"$(=Max(ColYear))"},ColWeek={"$(=Max(ColWeek)-1)"}>} [Quantity Collected])

[delbooth]

Thanks have copied and pasted  it into the KPI measure but it gives me a zero value

[Chanty4u]

try

=max(ColYear)  and see what value your  getting?

or else share sample app

[delbooth]

Thanks.

I believe the problem may be that the Colweek goes to 52 as I have data for 2017 (weeks 1-52) and the formula is looking up the year 2018 as max year and week 52 as that the max week in the filed.  Can you specify ColWeek = today's date week number.

Thnaks

Del

[delbooth]

Hi.

I used this in the end

Sum({<ColWeek = {"$(=Week(Today()))"}>}[Quantity Collected]) for current week

[Silambarasan1306]

That may be becas of you don't have values in the maximum week.

Try this,

Current week,

sum({< ColYear={"$(=Max({<ColWeek={'=[Quantity Collected]>0'}>}ColYear))"},ColWeek={"$(=Max({<ColWeek={'=[Quantity Collected]>0'}>}ColWeek))"}>} [Quantity Collected])

Previous week,

sum({< ColYear={"$(=Max({<ColWeek={'=[Quantity Collected]>0'}>}ColYear))"},ColWeek={"$(=Max({<ColWeek={'=[Quantity Collected]>0'}>}ColWeek)-1)"}>} [Quantity Collected])

If possible please share the sample application.