8 Replies Latest reply: Jan 17, 2018 5:31 PM by Tim Marsh RSS

    Trouble with dates and current selections in set expression

    Tim Marsh

      Hi all,

      I read about 20 articles about previous periods before posting this, as this seems to be a really simple problem someone will know how to solve.

      I can't post data, sorry, as I work in Health and our data is not de-identified and uses a data store.


      So, I am trying to create a simple table showing the sum of something for this month, compared to last month.


      My vMaxDate is 31/12/2017 at this stage.


      Using a straight table, I can get all the data to display for Nov2017 using the below. (I realise there are more elegant ways of doing this with flags, something I'll get to eventually.) I realise I may have some redundant syntax - be gentle, I am still learning.


      =sum({$<Date = {">=$(=monthstart(addmonths($(=vMaxDate),-1)))<=$(=monthend(addmonths($(=vMaxDate),-1)))"}, Day=>} DS_Complete_Counter)


      I can also display the current month using:


      =sum({$<Date = {">=$(=monthstart($(vMaxDate)))<=($(vMaxDate))"}>, Day=} DS_Complete_Counter)


      However, if I select a date, let's say Jun 3rd, 2017, I want the table to do 2 things.


      Previous month to show May and current month June.


      However, when I choose that date, both cols in the table are blank, so it looks like the current selection isn't working?


      Date is not a num, I have DateNum for that, and not stored as a dual.


      Happy to share Cal or anything else (I am using the standard MasterCal).


      Sorry to ask such a simple question.