Constant Formula Variable

[govind_ramesh]

Hi All,

Is there any possibility to create a formula field in Bex Query which will represent a constant value ?

Its like this :

We have the following Key figures : Customers with Sale | Count of this customers | Sales value | Cost value | Margin

But in here the customer count takes only those with sales. I need to show a field with the total customer count (all registered customers).

Hope I have made myself clear.

Regards

GKR

[govind_ramesh]

Dear Suman,Yasemin,

I tried this too.

Cust Mast <---Sales Cube

But Still i get the count of customers with sales.

Any suggestions ?

Regards,

[former_member182470]

Hi,

There is something wrong in the procedure. The idea will surely work. You have to use the Customer field by dragging from Customer Master Infoobject into the query designer. You should not use the Cust Infoobject which is there in Cube side.

My Sample : I have Master data left outer joined with DSO. I am using Customer from Master data in Query side.

[govind_ramesh]

Dear Suman,

I have done the same setting. I have created an infoset with the customer master as left joined with the sales cube. But still it is not being shown in the query. It is showing the count of customers for which there is a sale. That's all. Anyways I shall be looking into what or where exactly my system is misbehaving. Will update you all soon.

Regards

GKR

[yasemin_kilinc]

Hi Govind,

You can try this

1. Create a calculated key figure  where you can add 1 as a formula. In the aggregation tab select counter for all detailed values that are not zero.., select customer as reference characteristic.

2. Create a selection in your columns of the query. In the selection add the CKF you have created, add all other characteristics and select constant selection.

I think that will work.

Hope it helps

Yasemin...

[govind_ramesh]

Dear Yasemin,

The issue is that the query is a join on sales cube and customer master (Info Set). Hence the result is bringing in only those customers which are listed in the sales cube. Hence the total count is missing. I guess this is the case.

Regards

GKR

[yasemin_kilinc]

Hi govind,

I would suggest the same with Suman. You can count the number of customers using any key figure belonging to 0customer, if you use it as left operand in the infoset modeling.

Regards

Yasemin...

[former_member182470]

HI,

Hope you expect the same.

Cust with Sales I Count of this Cust I Total Customers ISales value | Cost value | Margin

Presently, you must have considered only invoiced or settled sales to calculate the Cust Count.

For Total Customers, you just need to remove that invoiced/settled restriction in your RKF and use this RKF in CKF to calculate Total Cust. Hope you must have done the same to show "Count of Cust" with Invoiced restriction. You jst need to remove this restriction and you can achieve your requirement.

Regards,

Suman

[govind_ramesh]

Dear Suman,

The issue is that the query is a join on sales cube and customer master (Info Set). Hence the result is bringing in only those customers which are listed in the sales cube. Hence the total count is missing. I guess this is the case.

Regards

GKR

[former_member182470]

Yeah, this must be the reason. But there is a workaround here.

You keep Customer Master as left operand and Sales Cube as right operand.

Infoset should be with Left outer join like  Cust Mast <---Sales Cube. So always all Customers list will appear even though some Customer codes are not matching with Sales cube along with matched customers. This will resolve your issue.

Regards,

Suman

[RamanKorrapati]

Hi Gr,

If you post your issue in Bex forums you may get asap help or more suggestions. This space related business content extractors.

Hope you understand.

Thanks