Month

[bharatkishore]

Hi All,

I am having Year field, MonthYear field, Sales org and Cat fields.

Now i have calculated one field called Adjcons.

Now i need to show value 0 where ever the month is jan and rest of logic should be same.

Attached qvw for reference. Please let me know if you need anything more.

Thanks,

Bharat

[tresB · Accepted solution]

This?

=If( num(right(Calendar_Month_Code,2))=1,0,Sum(ADJ_CONS_YTD_1))

[bharatkishore]

Can you please share the qvw.. bec in left join i am getting an error..

[tresB]

Use the above expression in your chart.

[bharatkishore]

thank you  for your reply. . But I need to do this back end because I needed that field to be converted into qvd

[tresB]

Few things need to be corrected. Try like:

left join(T6)

LOAD

          *,

          If(num(Month(Calendar_Month_Code))=1,0,YTD_IB) AS ADJ_CONS_YTD_1

;           // Preceding load

LOAD

      Sales_Organization,

      Cat,

      Country,

      AddMonths(Date#(Calendar_Month_Code,'YYYYMM'), 1)   as Calendar_Month_Code,

      YTD_IB

Resident T6 ;

[bharatkishore]

I have tried but i am getting as 0.

Your expression is working fine =If( num(right(Calendar_Month_Code,2))=1,0,Sum(ADJ_CONS_YTD_1)) and this is exactly giving me same output what i needed.

But i need to apply same logic in script and create a field.

Can you please help me on this.

[tresB]

What exactly have you got using the script I suggested? Could you post that file?

[bharatkishore]

Please find attached.

[tresB]

What if you try simply (without join):

T6:

LOAD Sales_Organization,

    Cat,

    Country,

    Calendar_Month_Code,

    Calendar_Year_Code,

    Sum(ADJ_CONS_YTD) as ADJ_CONS_YTD,

    sum(If(num(Month(Date#(Calendar_Month_Code,'YYYYMM')))=1,0,ADJ_CONS_YTD)) as ADJ_CONS_YTD1

FROM

C:\GCS\Development\RR\5_QVD\QV_intermediate_temp10.qvd

(qvd) group by Sales_Organization,Cat,Country, Calendar_Month_Code, Calendar_Year_Code ;