Issue in calculating last 3 months

[fashid]

Hi,

I have an issue with the following set analysis creatd by me or calculating last 3 months enquiry percentage

=Count({<FYear=,LOCAL_CURRENCY=, fMonth=, CalanderQuarter=,SERVICE_LINE={'EXP','WCM','OM'},CalendarDate ={">=$(today()-90)"}>}  DISTINCT ENQUIRY_ID)

/Sum({<FYear=,LOCAL_CURRENCY=, fMonth=, CalanderQuarter=,SERVICE_LINE={'EXP','WCM','OM'},CalendarDate={">=$(today()-90)"}>}  E.TARGET)

The problem is that this expression does not work

Please help

Regards,

Nadeem

[datanibbler]

Hi,

apart from the fact that CalanderQuarter is spelt wrong - CalendarDate is right - the set_expressions both look fine.

Have you tried them individually?`

Does the script_editor recognize them and display anything?

[prieper]

"does not work" can mean a lot of things,

what does not work?

Suggest to go step-by-step through this formula, i.e. check, whether

Count({<CalendarDate ={">=$(today()-90)"}>}  ENQUIRY_ID)

delivers a result. And then to continue (and check on spelling-mistakes)

Peter

[#4]

I have a file here that can help you, look the expression and script

[fashid]

Hi Peter ,

Thanks or replying.

I tried the way you told , and it does in fact display a result but when i change the no of days it does not change the result, infact when i remove the no of days completely it gives me the same result

eg =Count({<CalendarDate ={">=$(today())"}>}  ENQUIRY_ID) this expressions gave me the same result as

Count({<CalendarDate ={">=$(today()-90)"}>}  ENQUIRY_ID)

Regards,

Nadeem Shaikh

[fashid]

Hi Datanibbler,

Yes the script editor does recognise them the problem is that it displays the same result for -90 or -60 or -120.

Regards,

Nadeem

[prieper · Accepted solution]

Without a file it might be complicated in order to check field-formats, what is the result?

Have you tried to convert your date to number

Count({<CalendarDate ={">=$(NUM(today())-90)"}>}  ENQUIRY_ID)

Peter

[fashid]

Hi,

I have attached a sample test app for reference.

I guess i am  facing the issues in all the expressions .

For the MTD expression i have made below 2 expressions both of them lead to different results which one would be correct

1.

=Count({<FYear=,LOCAL_CURRENCY=, fMonth=, CalanderQuarter=,CONTROLLER={'WRITER'},SERVICE_LINE={'EXP','WCM','OM'}, CalendarDate={">=$(=MonthStart(Max(CalendarDate))))<=$(=Max(CalendarDate))"}>} DISTINCT ENQUIRY_ID)

/

sum({<FYear=,LOCAL_CURRENCY=, fMonth=, CalanderQuarter=, CONTROLLER={'WRITER'},SERVICE_LINE={'EXP','WCM','OM'},CalendarDate={">=$(=Num(MonthStart(Max(CalendarDate))))<=$(=Max(CalendarDate))"}>}E.TARGET)

2.

=Count({<FYear=,LOCAL_CURRENCY=, fMonth=, CalanderQuarter=,CONTROLLER={'WRITER'},SERVICE_LINE={'EXP','WCM','OM'}, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} DISTINCT ENQUIRY_ID)

/

sum({<FYear=,LOCAL_CURRENCY=, fMonth=, CalanderQuarter=, CONTROLLER={'WRITER'},SERVICE_LINE={'EXP','WCM','OM'},DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>}E.TARGET)

Please help

[fashid]

i have attached the file

[prieper]

Nadeem

first you may check on the consistancy of your queries

- guess you wish to include SERVCIE_LINE OMD, not OM

- formula for YTD calculates the entire year, not up to today, furthermore condition for SERVICE_LINE is missing in the first part

- What kind of various data you are going to sum? there is a difference between FYEAR and fYear?

In the last 30-days you again have used different dimensions, would advise to use also a numerical date and then use an expression like

CalendarDateNumberical ={">=$(=today()-30)"}

Peter