7 Replies Latest reply: Jul 20, 2015 9:34 AM by Sunny Talwar RSS

    exclude items variably based on actual date

      Hi,

       

      I have a problem with chart expression (chart shows margin progress during last three years) - i want to create a chart which shows all data until the date as follows:

      • IF(Day(Today())<15, show values until Month(Today())-1, (e.g. if today is 7.7.2015 exclude data booked after 31.5.2015)
      • IF(Day(Today())>15, show values until Month(Today()), (e.g. if today is 7.7.2015 exclude data booked after 30.6.2015);

       

      I can't do it by excluding month - it works but the QV excludes this month in every year. So I've created a variable

               =monthstart(Today()) hoping this excludes all data booked after 1st day of current month

      but it is not working - maybe because of the date format? I adjusted the format like D.M.YYYY in script so it fits the format of variable result but still no progress. And how to use the variable in case I need to exclude also data book after 31.5.2015? Using (-1) do not give the right result.

       

      Thank you.

      Lucie