Get data for current month in VC from query - Urgent

[Former Member]

Hi all,

I have designed a query for cube FIAR_C03 for overdue items and query output contains customer, net due date, document date, fiscal year period and amount. I want a chart for that query in VC that only contains those items in which net due date is in current month. How can i do that?

Where I can place the condition to display only those items in vc in which net due date is in current month and how ?

Please respond.

Regards,

Aisha Ishrat

ICI Pakistan Ltd.

[Former Member]

Resolved myself.

[Former Member]

Aisha,

I could not help you more. I am quiet busy down here in Brazil. Couldyou please post your solutions so others can access it?

Regards,

Gilson

[Former Member]

Hi Gilson.

Its ohk Gilson. I do understand. every one is busy at office . i also have so many deadlines in a week so no problem. You already helped me a lot.

I resolved this by giving a date range in this format.

date1 : date2

I was giving date in date1 - date2 format initially at which system was throwing error.

Regards,

Aisha Ishrat

ICI Pakistan Ltd.

[Former Member]

Hi Aisha,

You choose the variable of your choice. Test your query with 'current calendar day' for example or

'date'. What I am trying to say that you need a range, lets say from 01.2006 to 01.2008, in order to say: my defaut date is NOW.

But you have to test and find the date format of your variable in order to set up a formula in VC.

Regards,

Gilson Teixeira

[Former Member]

Hi Gilson.

I have created a new variable 'daterange' based on net due date and it is of type "Interval". The format it takes as i tested it in bex is " '1/1/2008' - '1/31/2008' ". It is working fine in BEX as it gives only those docs in which net due date is in giving range.

In VC , I have written following formula for net dute date :

'1.' & DSTR(NOW(),'MM') & '.' & DSTR(NOW(),'YYYY') & '-' & DSTR(NOW(),'DD') &'.' & DSTR(NOW(),'MM') & '.' & DSTR(NOW(),'YYYY')

When i see ouput in VC, it throws an application error that " Cannot execute B/W query ::Nested Exception. Failure to execute native function. Nested Exception".

Do you have any idea that what does this error mean? Is there any error in my VC formula??

Best Regards,

Aisha Ishrat

ICI Pakistan Ltd.

[Former Member]

No responses??

Regards,

Aisha Ishrat

ICI Pakistan Ltd.

[Former Member]

Hi Aisha,

I am quiet busy finishing a complex VC solution. Anyway. Try a very simple formula first.

Try to help you more during weekend.

Regards,

Gilson

[Former Member]

Hi Aisha,

It is possible to do with following steps:

1. In Query Design make your field 'Net Due' filter with variable for date input (0CALMONTH for example where your date should be this format MMYYYY). Test your query and see if you input a month/year date it would would work;

2. Build one table from output of your query (choose fields);

3. In Visual Composer, drag your query into your board and from 'Varaiables' port (NOT FROM INPUT) drag a line to create a Start point. As soon you realease to create 'Start' a box will ask which fields you want. Choose your date as field;

4. Select the line between Start point a query. In Input Field assigned the following formula (DSTR(NOW(),'MMYYYY') *

5. Deploy your model

• at this formula I said: transform NOW(), current date, in Text with format MMYYYY (012002 for example).

It works!

Assigned points if was helpful.

Kind Regards,

Gilson Teixeira

[Former Member]

Thanks Gilson.

Kindly tell me one more thing. I have placed 'Net Due Date' in filter. I am trying it to restrict it on variable 0CALMONTH but in varaiables 0CALMONTH is not available. How can i get that variable??

Thanks again.

Regards,

Aisha Ishrat

ICI Pakistan Ltd.

[Former Member]

Hi Aisha,

When you restric your 'Net Due Date' field, what type of variables you see? I said 0CALMONTH as a sample but what you really need is some kind of restriction because you need to set up the formula. Check the format (012002, 12032008 etc..) and tell me.

Regards,

Gilson

[Former Member]

Hi Gilson,

I can see following variables while restricting key due date :

key due date

key date of the query

day interval

planning key date

current calendar day

date

most current day in analysis - detail

current work day - sap exit

On which variable I should restrict it?

Thanks for your help.

Regards,

Aisha Ishrat

ICI Pakistan Ltd.