Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hey I am using this function in my table:
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)
+
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)
and I cant get the result as expected, it only show the value of this expression (the last one): SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)
what am I doing wrong?
What are you trying to do with
"date(YearStart(MAX(DATEPERIOD))-31)" ?
I want to get the amount from december 2015.
the range of my date period is december 2015 - December 2016
what I am trying to do with the whole formula is to find the average of amount last year and amount date selected.
i.e. the selected month is July 2016 then the average is: (amount as per Dec 2015 + amount as per July 2016)/2
if i run the sum individually, it works.
but when i add it together it doesnt give me the value as I expected
Have you tried creating separate expression for two expressions.. ?
if possible attach a sample application
what do you mean by creating separate expression?
Try with this expression in Text object and then see whether value is coming or not
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)
From Above, What if you select 02-01-2016(DD-MM-YYYY). Which value you want to get, I mean Arbitrary
Have you tried to separate them With parentheses?
(SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount))
+
(SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount))
yes I did. but still didnt work
when i run this in list box object:
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)
+
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)
it works perfectly. but when I try in pivot table it doesnt
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)
my date period only contain the last day of every month e.g. 31-12-2015, 31-01-2016, 29-02-2016,....., 31-12-2016
if I select 31-01-2016 I hope to get amount of 31-12-2015