topic Re: MTD Syntax Error in QlikView

MTD Syntax Error

Anonymous — Tue, 24 Mar 2015 13:31:06 GMT

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

Re: MTD Syntax Error

jonathandienst — Tue, 24 Mar 2015 13:45:24 GMT

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

Re: MTD Syntax Error

jonathandienst — Tue, 24 Mar 2015 13:47:50 GMT

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')

Re: MTD Syntax Error

Anonymous — Wed, 25 Mar 2015 08:02:43 GMT

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.

Re: MTD Syntax Error

jonathandienst — Wed, 25 Mar 2015 08:26:38 GMT

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.

Re: MTD Syntax Error

jonathandienst — Wed, 25 Mar 2015 08:28:45 GMT

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.

Re: MTD Syntax Error

Anonymous — Wed, 25 Mar 2015 08:45:48 GMT

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?

Re: MTD Syntax Error

Anonymous — Wed, 25 Mar 2015 08:46:32 GMT

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

Re: MTD Syntax Error

Anonymous — Wed, 25 Mar 2015 08:50:23 GMT

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...

Re: MTD Syntax Error

Wed, 25 Mar 2015 09:07:41 GMT

Hi,

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

Regards

Harsha

Re: MTD Syntax Error

Anonymous — Wed, 25 Mar 2015 09:42:12 GMT

Harsha,

I have checked but no such scenario.

Re: MTD Syntax Error

Wed, 25 Mar 2015 10:29:28 GMT

Hi Balraj,

Try with the below expression which is not having COMPANY = {'TCIL'}

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

If it is working fine then the remaining dates are not related with the COMPANY = {'TCIL'}.

Try it and let me know..

Thanks

Harsha

Re: MTD Syntax Error

Wed, 25 Mar 2015 10:46:08 GMT

Hi,

You can create a variable and pass today()-1

vCurrDate=today()-1 and by pass fiscal year and days,

Sum({$<Day=,Fin_Year=, Date={"<=$(vCurrDate)"}>}Value)

year([DATE OF ISSUE]) as year, day([DATE OF ISSUE]) as day

MTD =num(Sum({<year=,day=,[DATE OF ISSUE]={"<=$(vCurrDate)"}>} SALES)/VMillion,'00')

Regards

Vimlesh