3 Replies Latest reply: Jun 2, 2017 12:49 PM by Sunny Talwar RSS

    Set Analysis with a date field

    Colleen Spence

      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