Getting an error while using if statement in calculated column.can anyone please help me with this issue

[former_member197773]

I am trying to learn HANA on my own.i have product id,product name,delivery date and Grossamount in my calculated view.i am trying to create calculated column where i need Grossamount in two columns based on delivery date.I have 2012 and 2013 as values for my delivery date.so i have created two column as grossamount_2012 and grossamount_2013.if i have delivery date as 4thdec,2012 i want the grossamount value to be in coloumn grossamount_2012 and the grossamount_2013 should be blank.i have written an expression like this

if("Deliverydate" <= longdate(2012-12-04),"Grossamount","0")

and it looks like this is wrong.i am getting the text Grossamount rather than values for that field in my output.so can anyone help me please?

[former_member186082 · Accepted solution]

Hi Lokeswar,

What is the data type you assigned for the calculated measure? I feel its nvarchar/varchar. Hence a string value of "Grossamount" is coming as output. Assign same data type as the Grossamount field data type.

Also, check the names of the columns you map for true or false condition. Rather than you type them manually, just double click from the Columns folder from the bottom of the calculated measure. Hope this will solve the issue.

Let us know if you face any more issues.

Regards,

Chandra.

[former_member197773 · Accepted solution]

Hi Chandra,

Thanks for quick response...

I resolve the problem my own. Actually problem with date format.

if("Deliverydate" <= longdate(2012-12-04),"Grossamount","0")

Now, I changed date format as

if ("Deliverydate" <= longdate(2013,01,01),0)"Grossamount")

or i tried with 2013/01/01 also. It's working fine.

Regards

Lokesh

[former_member197773 · Accepted solution]

Hi chandra

i am trying to get the same result by using sql script and CE functions.i have written the following code

select A."PRODUCTID",

       E."TEXT" as "PRODUCTNAME",

       C."COUNTRY",

       D."DELIVERYDATE",

       Sum(D."GROSSAMOUNT") as "GROSSAMOUNT"

       from "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::EPM.MasterData.Products" as A     

       inner join "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::EPM.MasterData.BusinessPartner" as B

       on A."SUPPLIERID" = B."PARTNERID"      

       inner join "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::EPM.MasterData.Addresses" as C

       on B."ADDRESSID" = C."ADDRESSID"

       inner join "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::EPM.Purchase.Item" as D

       on A."PRODUCTID" = D."PRODUCTID"

       inner join "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::EPM.Util.Texts" as E

       on A."NAMEID" = E."TEXTID"

       GROUP BY A."PRODUCTID",E."TEXT",C."COUNTRY",D."DELIVERYDATE"; 

this is working fine but i want to split the grossamount based on current year and last year.Any idea how to do this

In calculation view using script can we use if and case statements?