7 Replies Latest reply on Sep 8, 2016 3:45 AM by Ganesh Vijay Kumar

# If Else calculated field with Blending Data

Hello Experts,

I am trying to calculate target achieved or not using if else function but not successful.

I have two data sources:

Data source 1 : Perfect Orders

Dimension used : TYPE Measure used : Overall Orders Formula used to calculated Perfect Order%

1-(SUM(ZN(if [TYPE]<>"OVERALL ORDERS" then ([COUNT_SCH_LINE]) end ))

/

SUM(ZN(if [TYPE]="OVERALL ORDERS" then ([COUNT_SCH_LINE]) end )))

Output will be as displayed below. Initially I had hard corded the target with the below mentioned formula. It was working well for me.

IF

[Perfect Orders] >= 0.9649

THEN

AVG(1)

ELSE

AVG(0)

END

Now, the problem is I have different targets for different region in an excel file. Finding it little difficult to write the formula by blending "Region". Attached is the excel file for your reference.

Now I tried the below mentioned formula, its not working any help in this regard is deeply appreciated!

IF ATTR([PM Target (Targets)].[KPI Name L2])="Perfect Order %"

AND

[Perfect Orders] >=1-SUM([PM Target (Targets)].[MET])

THEN

AVG(1)

ELSE

AVG(0)

END

Looking forward for positive response!

Thanks,

Ganesh

• ###### 1. Re: If Else calculated field with Blending Data

Hi Ganesh,

can you provide the data sources or twbx? In attached excel there is only 1 data source.

-Ashish

• ###### 2. Re: If Else calculated field with Blending Data

Hi Ashish,

Thanks for the response!

Please find attached twbx file for your reference.

Under Data source 1, I had created a calculated field named “ Perfect Order Flag” which was hard coded. Now I would like to use numbers in Datasource 2 and create the calculated field.

Please let me know if you need any other information.

Thanks,

Ganesh

• ###### 3. Re: If Else calculated field with Blending Data

Hello Ashish,

Did you get a chance to look at twbx file.

Thanks,

Ganesh

• ###### 4. Re: If Else calculated field with Blending Data

Hi Ganesh,

Could you attach a sample .xls file of Data Source 1?

Regards,

Dhwani

• ###### 5. Re: If Else calculated field with Blending Data

Hi Dhwani,

Please find attached Data source 1 named “Perfect Orders” and Data Source 2 named “Perfect Orders Target”.

Thanks,

Ganesh

• ###### 6. Re: If Else calculated field with Blending Data

Hi Ganesh,

I have attached the workbook, is this what you're looking for? As long as you are using the Region in the views, it will give you the target met for each region.

• ###### 7. Re: If Else calculated field with Blending Data

Hi Dhwani,

This worked for me! Thanks a ton for your help☺

Regards,

G