Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
2 parts to this post, Part 1:
I have a fiedl, [1L Date], which is a date field. I am using the following Set Analysis expression
=SUM({<[1L Date]={"<=DATE(AddYears(today(),-1))"}>} quantity)
I am trying to get the sum of all quntities in the last 365 days
Part 2
Same as Part 1, except I want the Sum of all Quantities from the previous 365 days so something like
=SUM({<[1L Date]={">=DATE(AddYears(today(),-2))", [1L Date]={"<=DATE(AddYears(today(),-1))"}>} quantity)
Both expressions are returning 0 at the minute, please can someone help,
Kind Regards,
Miles
Hi Miles,
You can use the addmonths() in case you want to get the sum for the Prior 12 months or more.
//Num#(Sum({<YearMonth1 ={">=$(=Date(addmonths(Max(YearMonth1), -11), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth1), 0), 'MMM-YY'))"} >}Quantity)).
This will give the sum for last one year sale from Todays date.
First Calculate YearMonth in the Script where you are calculating Year and other date field
(Month(date)&'-'& right(Year(date),2)) as YearMonth,
and than take the resident of that table,
and calculate
YearMonth1
Date(Date(Date#(YearMonth,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth1.
This is done because we want the YearMonth Format to be in number as used in the Front-End.
Hope this help you
Regards
Kamal
Miles
Try these
=Sum({<[1L Date]={"<=$(=DATE(AddYears(today(),-1)))"}>} quantity)
=Sum({<[1L Date]={">=$(=DATE(AddYears(today(),-2))) <=$(=DATE(AddYears(today(),-1)))"}>} quantity)
Hope that helps
Jonathan
Hi Jonathan,
Thanks for your reply, unfortunately this still comes up with a 0, any more ideas?
Kind Regards,
Miles
Hello Miles,
Does those fields have an actual (numeric) date field so the value returned by the function Date() is exactly the same as the stored in the field? Is there any chance that there might be some differences because of the regional settings in Windows?
Miguel
Miles
Where are you trying to use these expressions - in a text box or in a dimensioned chart - the above will work in the former, but possibly not in the latter depending on your data model and the chart dimensions?
And are you selecting any date related fields? That field selection may be limiting the data returned.
Have you tested the expression with literal dates (work out the date corresponding to the calculation, eg
=Sum({<[1L Date]={"<=2011/10/11"}>} quantity)
Finally, do you actually have data in the date ranges being selected?
Regards
Jonathan
Hi guys,
Thank you for your support. I have been playing around with trying to get these values to work in a textbox first before jumping ahead and putting it into a table, but I got this formula to work,
=sum({1<[1L Date]={">=$(=addyears(max([1L Date]=))-3) <=$(=min([1L Date]))"}>}quantity)
BUT I don't know why this works and Jonathan's suggestion didn't, please can someone explain so I can learn?
P.S. in terms of regional settings, it is a possibility that something like this might be happening as I am dragging data over from a US server...it could be an explanation, in which case how would you recommend I combat that?
Kind Regards,
Miles
Hi Miles,
Just try like this....
In case you want to calculate YTD-
=num(sum({<[Month]=,[Year]={$(=max([Year]))},[Date]=
{"<=$(vCurrDate)"}>}[Stock]))
where vCurrDate is a varible and it's value will be --date(today()-1) or date (today())
Again
for LYTD
=num(sum({<[Month]=,[Year]={$(=max([Year])-1)},[Date]=
{"<=$(vCurrDate)"}>}[Stock]))
Hope this help you
Regrads
Kamal
Hi Kamal,
That's great in terms of last 12 months to date, but in terms of getting the previous 12 months prior to that why is it when I change the formula to this:
=num(sum({<[Month]=,[Year]={$(=max([Year])-2)},[Date]={"<=$(vPriorYearDate)"}>}quantity))
Where vPriorYearDate is todays date minus 1 year,
it doesn't work...any ideas?
Kind Regards,
Miles
Hi Miles,
You can use the addmonths() in case you want to get the sum for the Prior 12 months or more.
//Num#(Sum({<YearMonth1 ={">=$(=Date(addmonths(Max(YearMonth1), -11), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth1), 0), 'MMM-YY'))"} >}Quantity)).
This will give the sum for last one year sale from Todays date.
First Calculate YearMonth in the Script where you are calculating Year and other date field
(Month(date)&'-'& right(Year(date),2)) as YearMonth,
and than take the resident of that table,
and calculate
YearMonth1
Date(Date(Date#(YearMonth,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth1.
This is done because we want the YearMonth Format to be in number as used in the Front-End.
Hope this help you
Regards
Kamal