MTD Syntax Error

[Anonymous]

Dear all,

Could you suggest syntax error in following expression:

=num(Sum({<[DATE OF ISSUE]={">=$(=Num(MonthStart(Max([DATE OF ISSUE]))))<=$(=Max([DATE OF ISSUE]))"},COMPANY={'TCIL'}>} SALES)/VMillion,'00')

Regards,

Balraj

[jonathandienst]

The syntax looks OK to me. What sort of error are you getting?

Remember that field names and variable names in QV are case sensitive, so VMillion is not the same as vMillion.

You may have a problem with date formats. Is [DATE OF ISSUE] formatted as a date or a number? You may need something like:

=num(Sum({<[DATE OF ISSUE] = {">=$(=Date(MonthStart(Max([DATE OF ISSUE])))) <=$(=Date(Max([DATE OF ISSUE]))"}, COMPANY = {'TCIL'}>} SALES) / VMillion, '00')

HTH

Jonathan

[jonathandienst]

Although the second term of the [DATE OF ISSUE] selection is redundant, so you could simplify to:

=num(Sum({<[DATE OF ISSUE] = {">=$(=Date(MonthStart(Max([DATE OF ISSUE]))))"}, COMPANY = {'TCIL'}>} SALES) / VMillion, '00')

[Anonymous]

Dear Jonathan,

This expression is working fine but it is not working in MTD correctly when I select a specific date.

Say: If I select a date of 07/Dec/2014 then my MTD should give me sale from 01/Dec/2014 to 07/Dec/2014. But with above expression it is giving me of 07/Dec/2014 only.

Pls suggest.

[jonathandienst]

Are you selecting [DATE OF ISSUE] or a related date?  If so, you might need to override that field's selection in the set expression.

Otherwise, I suggest that you post a sample for more detailed analysis.

[jonathandienst]

Or is DATE OF ISSUE a dimension? The selection will reduce the dimension before calculating the expression. You may need a calculated dimension to override the selection. You will also need a TOTAL in the expression to override the DATE OF ISSUE dimension.

[Anonymous]

Yes, I am selecting Date of Issue only from which I have created my Master Calender.

You mean to say, I need to bypass in set expression or some thing else?

[Anonymous]

No, Its not a dimension but yeah I have created my master calender with this date only.

[Anonymous]

If I am selecting a specific date from master calender, it is giving me 0. but it should return minimum date of selected date and till selected date...

[#10]

Hi,

Check remaining dates are having COMPANY = {'TCIL'} or not?

Regards

Harsha