how to calculate year to date in set expression

[oykyes]

I  choose a date field(my date  type in fiter part will be mm.yyyy) and it will show sum(price) for all months less than or equal to the month  in same year that I choose in filter. for example I choose 12.2019. Price must be calculated  for months between 01.01.2019 - 31.12.2019.But there is an exception. If I dont make any selection, the graph should show the recent values. For example. Today is 03.01.2020 and january is not over yet so when I open the sheet and make no selection, I should see december 2019. December 2019 means sum price for interval 01.01.2019-31.12.2019

So I write it this way.

(in load script I defined period as -->Date(MonthStart(Date(floor(MYDATE),'DD.MM.YYYY')),'YYYYMM') as PERIOD)

if(DATE(AddMonths(MONTHEND(Today()),-1),'DD.MM.YYYY')-DATE(MONTHEND(max(PERIOD)-1),'DD.MM.YYYY')=0,
num(sum({<MYDATE={">=$(=yearstart(MAX(date(MYDATE,'DD.MM.YYYY'))-1))<=$(=date(max(PERIOD)-1,'DD.MM.YYYY'))"}
>}price),'#.##0 TL'),
num(sum({<Year(MYDATE)={"$(=getfieldselections(Year([PERIOD])))"},
month(MYDATE)={"<=$(=getfieldselections(month([PERIOD])))"}
>}price),'#.##0 TL'))

num(sum({<MYDATE={">=$(=yearstart(MAX(date(MYDATE,'DD.MM.YYYY'))-1))<=$(=date(max(MONTHEND(PERIOD)),'DD.MM.YYYY'))"} 
>}price ),'#.##0 TL')

It didnt work so I tried to use getfieldselection function.

if(count(GetCurrentSelections()>0),

num(sum({<
MYDATE={">=$(=yearstart(MAX(date(MYDATE,'DD.MM.YYYY'))-1))<=$(=date(max(MONTHEND(PERIOD)),'DD.MM.YYYY'))"}
>}price ),'#.##0 TL'),
num(sum({<
MYDATE={">=$(=yearstart(MAX(date(MYDATE,'DD.MM.YYYY'))-1))<=$(=date(max(PERIOD)-1,'DD.MM.YYYY'))"}
>}price),'#.##0 TL'))

Can I write something like that??

Year([PERIOD])={"$(=getfieldselections(year([PERIOD])))"},month([PERIOD])={"<=$(=getfieldselections(month([PERIOD])))"}

[lorenzoconforti · Accepted solution]

Hi, see attached

The first thing is that I would avoid applying any particular date formatting in the loading script; makes the set expression easier

Then, I would create a separate (unlinked) table that contains the months (PERIOD) to be used to make selections (so that the current set is unaffected)

Then an if condition on whether a specific period has been picked and, depending on this, two different set expressions would be evaluated

What you are looking for is the expression in the text box "Combined"; as you select a period in the PickPeriod field, the calculation will update as expected (do not filter by the PERIOD field otherwise it will impact your set)

[oykyes]

Thank you so much for your help Lorenzo! Using PickPeriod as a new variable is a good idea... It worked 🙂