count number of days in a month (query designer)

[Loed]

Hi guyz,

How can I solve this problem? Is it possible to calculate in query the total number of days for the past three (3) months?

For example, I have these objects in my query:

Char:

0CALMONTH - with variable

0CUSTOMER

KF:

ZSALES

NOTES:

- 0CALMONTH has a variable (mandatory) to select a month

- There should be a FORMULA, let's say FINAL_SALES, which will contain this formula:

               FINAL_SALES = ZSALES / (number of days for the last three months)

For instance, after running the query, I choose 05/2013 in my 0CALMONTH object, the formula should be:

               FINAL_SALES = ZSALES / 89

Since February 2013 has 28 days, March has 31 days, and April has 30 days, so 28+31+30 = 89.

SAMPLE FLAT FILE DATA:

0CALMONTH          0CUSTOMER               ZSALES

201305                   Customer A                  1000

201305                   Customer B                  1500

201305                   Customer C                  1800

201306                   Customer A                  300

201306                   Customer B                  500

201306                   Customer C                  850

This should be the OUTPUT:

                              MAY 2013

0CUSTOMER          ZSALES

Customer A             11.24

Customer B             16.85

Customer C             20.22

So if I choose 06/2013, after running again the query, the formula should be:

               FINAL_SALES = ZSALES / 92

Since March has 31 days, April has 30 days, and May has 31 days, so 31+30+31 = 92.

This should be the OUTPUT:

JUNE 2013

0CUSTOMER          ZSALES

Customer A             3.26

Customer B             5.43

Customer C             9.24

Is this possible in CMOD? If so, how will I do it? This will be my first time to use CMOD..

I have a solution to this one but I'm wondering if this is possible in query designer or by the use of CMOD..

Thank you!

Loed

[tmsyed]

Hello,

I'm reviving this very old thread because I have the same requirement, but I cannot find any way to point to the 0CALMONTH attribute "Number of days" when I create a Replacement path variable in SAP HANA Studio 2.3.71.

Is it no longer possible in this version?

Regards / Tariq

[Loed]

There is Tariq.

Try to replace the VARIABLE you have entered, perhaps it is not permitted.

Thanks.

Loed

[tmsyed]

Thanks for sharing the screenshot Loed 🙂

I think it is the permission issue, as you suggested, because I don't have the same options as yours.

Thanks 🙂

Tariq