Help with Date Set analysis

[hopkinsc]

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?

[tresesco]

Try putting " " like: Sum({<Trans_Date={"$(=max([Reporting Year Index))"}>}Sales)

If the date format matches, this should work.

[jonathandienst]

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

[hopkinsc]

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

[jonathandienst]

With the additional information:

{<Trans_Date = {">=$(=YearStart('1/12009', max([Reporting Year Index]))) <=$(=YearEnd('1/12009', max([Reporting Year Index])))"}>}

Edit - forgot the max()

[#6]

Change your expression as follows:

Sum({<Trans_Date={$(#=max([Reporting Year Index))}>}Sales)

[tresesco]

Try using p() like:

=Sum({<[Reporting Date]=p({<[Reporting Year Index]={"$(=Max([Reporting Year Index]))"}>})>}Sales)

PFA

[hopkinsc]

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)

[hopkinsc]

Thanks, that also doesnt work.

I have attached a sample..

[#10]

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