How to get absolute measures in table without constraints of certain dimensional fields

[Vince_CH]

Dear Community,

I have a straight table like below, with offers value and realzied value, hence with offer realization rate. Dimensions are with offer fiscal year and fiscal month included.  when as a complete table, it works fine with each offer realization rate indicated like showed.  But when I try to select one fiscal year or month, the realzation turned into zero.  Because of the constraints of dimensional offer fiscal year was not in same pace with the realized order year possibly.

My question is, how to exclude the constraints from the certain dimensional fields, and only keep the absolute value?

I have seen similar posts somewhere earlier, but I couldn't find them now, look foward to some hints? thanks a lot!

[Vince_CH]

Dear All, I have tried following expressions with what in brackets, it seems okay now. thanks!

Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])

[Vince_CH]

Dear All,

Sorry, I still have some problem with table need support.  the problem is like following, as per total offervalue and total realized order value, the realization rate should be 31.5% intead of current 178.5%.  the expressions of the offervalue, order value, and realization rate are as following:

Offer: Aggr(Sum(Offer_Total_Value_Net_Local_Curreny),[Offer No.])

Oder: Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])

Rate: Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])/
Aggr(Sum(Offer_Total_Value_Net_Local_Curreny),[Offer No.])

Dimensions are fiscal year and fiscalmonth as per offer calendar, and i have omitted null in dimension field of [offer no]. how can I recitify this issue?

@pradosh_thakur 

[pradosh_thakur]

Why haven't you included {<FiscalYear_Off=,FiscalMonth_Off=>} in the offer value expressions?

And what is the total expression for the "realization rate" ? Is it auto or sum  or count ?

[Vince_CH]

Hello Pradosh, the reason i didn't add {<FiscalYear_Off=,FiscalMonth_Off=>} for offer calcualation, is because the dimensions are already in offer fiscal year and month, so it is no problem there.

Total expression of "realization rate" is : Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])/ Aggr(Sum(Offer_Total_Value_Net_Local_Curreny),[Offer No.])
It is in average, when I change it into auto, it shows zero, by sum it is to show sumup of all percentage which is not correct.

[pradosh_thakur]

try column(2)/column(1) and change it to auto may be

or

if(dimensionality()<>0, your expression, aggr(your expression, your dimension))

[Vince_CH]

Hello Pradosh, I have tried both ways, in first way, each order result is same, with total if I changes into auto, it became zero too.
in second option, i have modied the expresssions like:
If([Offer No.]<>0,Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])/Aggr(Sum(Offer_Total_Value_Net_Local_Curreny),[Offer No.]),
Aggr(Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])/Aggr(Sum(Offer_Total_Value_Net_Local_Curreny),[Offer No.]),
FiscalYear_Off,FiscalMonth_Off,[Offer No.],[Order No.]))
It showed the same results.

[pradosh_thakur]

@Vince_CH  where is the dimensionality() in your expression?

try below

If(dimensionality()<>0, ,Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])/Aggr(Sum(Offer_Total_Value_Net_Local_Curreny),[Offer No.]),
Aggr(Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])/Aggr(Sum(Offer_Total_Value_Net_Local_Curreny),[Offer No.]),
FiscalYear_Off,FiscalMonth_Off,[Offer No.],[Order No.]))

or

If(dimensionality()<>0, ,Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])/
Aggr(Sum(Offer_Total_Value_Net_Local_Curreny),[Offer No.]),
Aggr(Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])/
Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Offer_Total_Value_Net_Local_Curreny),[Offer No.]),
FiscalYear_Off,FiscalMonth_Off,[Offer No.],[Order No.]))

[Vince_CH]

I tried with both, it seems no difference between above two?
the results kept same as before, it cannot be automatically sum up yet.
the bug might be somewhere else?

[pradosh_thakur]

@Vince_CH  I am sorry to not reply earlier as i didn't get the alert of the comment. Can you please upload a sample app.Let ushave  a look.

Thanks

Pradosh