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:




      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?