Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, i need some help with some set analysis.
I have a field 'Reporting Year Index' which is currently '6' for this year.
I am trying to get some set analysis working which will sum YTD sales.
Sum({<Trans_Date={$(=max([Reporting Year Index))}>}Sales)
Can anyone see what i am doing wrong please?
Try putting " " like: Sum({<Trans_Date={"$(=max([Reporting Year Index))"}>}Sales)
If the date format matches, this should work.
Hi
This will work if Trans_Date is equal to 6, but I suspect it is a date value.You are missing the closing ] for the reporting year index.
Assuming Trans_Date is a date. you will need to convert the reporting year index into a date, but you have not provided enough information. Again assuming that there is a base date for the index, you could try
{<Trans_Date = {">=$(=YearStart(vBaseYear, [Reporting Year Index])) <=$(=YearEnd(vBaseYear, [Reporting Year Index]))"}>}
HTH
Jonathan
Hi, thanks.
That doesnt work as the [Reporting Year Index] field is an index number not a date.
so i want the Trans_Date field to use all dates that are linked to the max reporting year index field if that makes sense.
Reporting Year Index Reporting Date
5 01/01/2013
5 15/05/2013
6 27/12/2013
6 28/12/2013
6 01/01/2014
6 17/01/2014
So in the above example the max reporting date index is 6
so i would want Trans_Date to include any date with a Reporting Year Index of 6.
Thanks
With the additional information:
{<Trans_Date = {">=$(=YearStart('1/12009', max([Reporting Year Index]))) <=$(=YearEnd('1/12009', max([Reporting Year Index])))"}>}
Edit - forgot the max()
Change your expression as follows:
Sum({<Trans_Date={$(#=max([Reporting Year Index))}>}Sales)
Try using p() like:
=Sum({<[Reporting Date]=p({<[Reporting Year Index]={"$(=Max([Reporting Year Index]))"}>})>}Sales)
PFA
Hi Janathan,
Thanks for your help. I can't get that to work.
This is my actual expression...
sum(
{<Status_Name={'Rejected','Job Completed','Not Completed'}
,[2ndStatus]={'Defect Not As Described'}
,Status_Date={">=$(=YearStart('1/12009',[Reporting Date Index]))<=$(=YearEnd('1/12009',[Reporting Date Index]))"}
,CommentDate={">=$(=YearStart('1/12009',[Reporting Date Index]))<=$(=YearEnd('1/12009',[Reporting Date Index]))"}>}
1)
Thanks, that also doesnt work.
I have attached a sample..
I think the basic sub-expression are wrong in your expression.
=sum(
{<Status_Date={">=$(=YearStart('1/12009',[Reporting Date Index]))<=$(=YearEnd('1/12009',[Reporting Date Index]))"}
,[Comment Date]={">=$(=YearStart('1/12009',[Reporting Date Index]))<=$(=YearEnd('1/12009',[Reporting Date Index]))"}>}
[Job Count])
1) You are using [Reporting Date Index] in exp, but in the sample file we have [Reporting Year Index].
2) The date passed to YearStart function is '1/12009', which is not a valid date.
Can you please break your expression and then embed into one?
Thanks,
Angad