Set Analysis with a date field

[#1]

I've managed to get my chart expression using the if statement, but it doesn't seem to work in set analysis.

My if statement is:

sum(if(WEEK_COMMENCING_Home=date('$(vHomeLatestWeek)','DD/MM/YYYY'),PERCENT_14DAYFCR_home_week))

where in the data editor I have defined

Let vHomeLatestWeek = Date(vMaxHomeWeek,'DD/MM/YYYY');

In Set Analysis I have tried:

sum({<WEEK_COMMENCING_Home={date('$(vHomeLatestWeek)','DD/MM/YYYY')}>} PERCENT_14DAYFCR_home_week)

but this does not work.

Additionally, there is only one value of PERCENT_14DAYFCR_home_week per WEEK_COMMENCING_Home, can I use another function? I thought about the peek function but vHomeLatestWeek will not always be the last row in the table. I don't really need to sum anything, I just want to return the value for PERCENT_14DAYFCR_home_week when it is a certain value of WEEK_COMMENCING_Home. I have tried the only() function without success, the problem seems to be related to the date formatting?

Thanks

Colleen

[sunny_talwar]

Your syntax is not correct, try this

Sum({<WEEK_COMMENCING_Home = {"$(=Date(vHomeLatestWeek, 'DD/MM/YYYY'))"}>} PERCENT_14DAYFCR_home_week)

Also, check this out

Dates in Set Analysis

[#3]

Hi Sunny,

With that syntax I just get a 0 value (rather than the actual value). If I change the aggregation to min or max (as there is only one row with the supplied date) I get no output at all.

[sunny_talwar]

Would you be able to share a sample so that we can take a look at this?