How to restrict the keyfighure camparing two dates

[former_member410049]

Hi Experts,

We have a requirement that we need get the no of invoice documents which paid on time

ie of clearing date<= net due date then it is paid on time.

do we get this in bex query designer.

Regards

Phani Chaithanya

[Former Member · Accepted solution]

Hello,

you cannot use 2 CKF for your scenario. You have add condition in CKF1 for getting your result. Now you got total invoice count. From here you have to use a condition for getting the invoice count based on date.

Regards,

Priya M

[former_member410049 · Accepted solution]

HI priyadarshini,

Thank you for the reply.The issue is resolved.

Regards

Phani Chaithanya

[Former Member]

Hi Phani,

Can I suggest a different approach?

May be you can do this comparation during the load inside the transformation.

Also you can create a new Charact with a X for documents payed on time.

By doing this, you can create a Query with only X values.

Two key points to do this. It's easier to create this formula in transformation and this new charact gives you a lot of flexibility in any query or filter or conditions.

Hope this helps.

Best Regards.

[former_member410049]

HI Fernandaz,

Thank you for the reply,first i had tried by handling it in end routine based on fiscal period and invoicedocno the values are coming correct fiscal period wise but we need  ytd and previous ytd in the report so we are getting duplicate count values for YTD .

So i have thought of doing it at Rport level rather than in Backend.

Regards

Phani Chaithanya

[Former Member]

CKF1 = Formula1 * 1

First try with this.you will get the total invoice number count.

if you get the document number count. then proceed with the formula.

net date and clearing you are directly adding it in condition or using it as variable?

Regards,

Priya M

[former_member410049]

I am getting the invoice number count.

I am using formula variable for the condition but iam getting xx values for the second ckf.

Regards

Phani Chaithanya

[Former Member]

The formula which you used is not correct.

Regards,

Priya M

[former_member410049]

Can you Please tell me how to write the formula for the condition

Thanks & Regards

Phani Chaithanya

[Former Member]

Did you tried the formula which i told?

Because, when we use formula, it is very difficult to predict the correct formula. step by step we need to find out the formula syntax.

Give your formula variable definition.

Regards,

Priya M

[former_member410049]

i have created two formula variables for cleardate adn netdue date and created key fighure wto get the total of invoice no.

for 2 ckf i have used formula (cleardate<=netduedate)*ckf1 

for this iam getting x values.

Regards

Phani Chaithanya

[Former Member]

No. Inside the CKF you have to use the formula variable.

CKF1 = Formula1 * 1

First try with this.you will get the total invoice number count.

After that give the date condition and check.

Regards,

Priya M.

[Former Member]

Hello,

Steps to follow ,

1) Create a formula variable with replacement path. The reference characteristics should be Invoice document number. Options replacement with attribute  value and constant is 1.

2) Create a CKF variable and add the date condition and formula variable which is created in the step 1.

3) CKF aggregation should be "After aggregation", set decimal as '0'.

Regards,

Priya M

[former_member410049]

Hi Priyadarshini,

I had did the same but iam getting x values in the report.

Regards

Phani Chaithanya

[former_member410049]

the formula is (Clearingdate<=netduedate)*ckf1 is this correct Priyadarshini.

Regards

Phani Chaithanya

[Former Member]

Hello,

If i understand well, you want to display the count of invoice doc number based on clearing date<= net due?

Regards,

Priya M

[former_member410049]

Yes Priyadarhini

[Former Member]

Hello,

Based on which characteristic, the counts needs to be displayed on date basis with condiition.

Regards,

Priya M

[former_member410049]

Hi Priyadarshini,

Thankyou for Reply.Based on invoice doc no we ned to disply the the count and also lineitems wise we need to display the count

[RafkeMagic]

either map those dates as key figures

or use formula variables to have them available as key figures in the query

[former_member410049]

HI Raf,

Thanks you for the reply

How to use formula variable to check the conditionfor this dates

Regards

Phani CHaithanya