Concat strings and formula in count if condition

Frinolav — Thu, 28 Oct 2021 13:15:43 GMT

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 🙂

Re: Concat strings and formula in count if condition

chrismarlow — Thu, 28 Oct 2021 19:00:18 GMT

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.