Skip to Content
avatar image
Former Member

How to restrict the keyfighure camparing two dates

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

11 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 18, 2016 at 09:46 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 15, 2016 at 04:18 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • Apr 15, 2016 at 06:05 AM

    either map those dates as key figures

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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      HI Raf,

      Thanks you for the reply

      How to use formula variable to check the conditionfor this dates

      Regards

      Phani CHaithanya

  • avatar image
    Former Member
    Apr 15, 2016 at 06:40 AM

    Hello,

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

    Regards,

    Priya M

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • avatar image
    Former Member
    Apr 15, 2016 at 09:11 AM

    Hello,

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

    Regards,

    Priya M

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 15, 2016 at 09:45 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 15, 2016 at 10:47 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 15, 2016 at 10:51 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 15, 2016 at 11:13 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 15, 2016 at 11:14 AM

    The formula which you used is not correct.

    Regards,

    Priya M

    Add comment
    10|10000 characters needed characters exceeded