Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])))"}
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)
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)
Thank you so much for your help Lorenzo! Using PickPeriod as a new variable is a good idea... It worked 🙂