
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to get absolute measures in table without constraints of certain dimensional fields
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!
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aggr(Sum({<FiscalYear_Off=,FiscalMonth_Off=>}Order_Total_Value_Net_Local_Curreny),[Order No.])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try column(2)/column(1) and change it to auto may be
or
if(dimensionality()<>0, your expression, aggr(your expression, your dimension))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
the results kept same as before, it cannot be automatically sum up yet.
the bug might be somewhere else?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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

- « Previous Replies
-
- 1
- 2
- Next Replies »