Since Last End Day SLED

[#1]

Hi Everyone,

I have a file from which I want to get the amount for the SLED or Since Last End Day

But I can't figure out how to get it ?

I have tried a bunch of stuff that didn't work like the following

Sum({$<[GLDET.GLD_AMT]=P({1<Date={$(=Day(Max([GLDET.GLD_DATE]))}>}[GLDET.GLD_AMT])>}[GLDET.GLD_AMT])

Sum({<Year=, Month=, Day=,[GLDET.GLD_DATE]={">=$(=Num(LastWorkDate(Max([GLDET.GLD_DATE]))))<=$(=Day(Max([GLDET.GLD_DATE])))"}>} [GLDET.GLD_AMT])

so the date comes from [GLDET.GLD_DATE] and the amount comes from [GLDET.GLD_AMT]

I also want the YTD and MTD. I have used the following that did work but I want it to stay as year and as month even if the whole date is selected because currently if in the filter Year is selected then month then day all the 3 columns converts into the same number.

for YTD:

Sum({<Year=, Month=,Day=,[GLDET.GLD_DATE]={">=$(=Num(YearStart(Max([GLDET.GLD_DATE]))))<=$(=Max([GLDET.GLD_DATE]))"}>} [GLDET.GLD_AMT])

for MTD:

Sum({<Year=, Month=,Day=, [GLDET.GLD_DATE]={">=$(=Num(MonthStart(Max([GLDET.GLD_DATE]))))<=$(=Max([GLDET.GLD_DATE]))"}>} [GLDET.GLD_AMT])

Thanks in advance.

Noha

[sunny_talwar]

By means of an example, can you show what SLED means? I understand it is short name for Since Last End Day, but not sure what date range it will corresponds to?

[Anonymous]

may be check with your date format  and include "1"

Sum( 1 {<Year=, Month=,Day=,[GLDET.GLD_DATE]={">=$(=Date(YearStart(Max([GLDET.GLD_DATE]))),’MM/DD/YYYY’)<=$(=Date(Max([GLDET.GLD_DATE]),’MM/DD/YYYY’)"}> }   [GLDET.GLD_AMT] )

[#4]

it should be since yesterday closure till today

[#5]

Thank you for your help but that gave me dashes

my formula I wrote gives me the following table

but as you can see all have the same value when there is no selection made in the filter and also gives me the same look when I choose the year month and day in the filter

[sunny_talwar · Accepted solution]

Maybe something like this:

Sum({1<Date={"$(=Date(DayStart(Max([GLDET.GLD_DATE])), 'DateFieldFormatHere'))"}>} [GLDET.GLD_AMT])

Replace the placeholder for date format with your date field format (DateFieldFormatHere)

[#7]

Thanks sunny but  It is showing no movement even if day changed

[sunny_talwar]

Which day changed? Where are you making a selection?

[#9]

I am saying it didn't change it cancelled the selections but what I am hoping is that if they today selected for example 25 of april 2016 the SLED will show the the day 24-25 and the MTD shows the from 1-25 and the year will show from Jan- april

[sunny_talwar]

That is what it should do. Not sure what isn't working. Would you be able to share a sample?