Concat strings and formula in count if condition

[Frinolav]

Hi everyone,

I have access to a database that cannot be modified with a column giving the week number as follows "2018 W1", "2021 W40", etc etc

I want to create a KPI giving the distinct numbers of event generated during the previous week and I do not know the syntax to do it : 

Measure : Count({<[Event Creation Date.Calendar.Week]={"year(today()) & ' W' & week(today())-1"}>} distinct [Event ID])

And the output result is always 0

Can you please help ?

Thanks 🙂

[chrismarlow]

Hi,

It does not seem to like trying to subtract 1 from the week & then bolt together as a string ... could you try taking off 7 days & taking  the week?

Count({<[Event Creation Date.Calendar.Week]={"$(=year(today())& ' W' & week(today()-7))"}>} distinct [Event ID])

Cheers,

Chris.