Calculated dimension expression.

[rohitians]

Hello,

The Following is the expression for opening balance i am using,

Sum({$<FinancialYear=,MonthName=,Quarter=,PostingDate ={"<$(=(Min(PostingDate)))"}>}  Cost_Amt_Actual)+

Sum({$<FinancialYear=,MonthName=,Quarter=,PostingDate ={"<$(=(Min(PostingDate)))"}>}  Cost_Amt_Expected)/100000

Can i use this Expression as calculated dimension,

what changes do i need to make so that it works..

Thanks and Regards,

Rohit

[sujeetsingh]

try using =(your expression) as calculated dimension

[rohitians]

Hello SOS,

I tried its showing error in calculation...

Thanks and Regards,

Rohit

[#4]

Please post sample application..

[tresesco]

Set Analysis can't be used in calculated dimension. When you are using calculated dimension, that means you are telling the customized members of any dimension, so the out put is expected to be a set of members of that dimension and NOT any amount (which your expression is generatiing here). i believe, you have to use your expression in expression and some conditional statement for calculated dimension. Hope someone proves my understanding wrong.

Thanks.

[#6]

hi

calculated dimension only uses on one field. so your expression not working in calculated dimension. because in your expression you calculate on multiple dimension.

Regards

[rohitians]

HELLO,

I have this inventory report where in after i introduse month as dimension i am getting opening and closing inventory values month wise wrong,

wheras overall toatll is right...I am attaching the Excel file for you to see the format....

For opening following is the code

=(Sum({$<FinancialYear=,PostingDate ={"<$(=(Min(PostingDate)))"}>}  Cost_Amt_Actual)+

Sum({$<FinancialYear=,PostingDate ={"<$(=(Min(PostingDate)))"}>}  Cost_Amt_Expected))/100000

for closing

=(Sum({$<FinancialYear=,PostingDate ={"<$(=(Max(PostingDate)))"}>}  Cost_Amt_Actual)+

Sum({$<FinancialYear=,PostingDate ={"<$(=(Max(PostingDate)))"}>}  Cost_Amt_Expected))/100000

Thanks and Regards,

Rohit

[rohitians]

HELLO,

I have this inventory report where in after i introduse month as dimension i am getting opening and closing inventory values month wise wrong,

wheras overall toatll is right...I am attaching the Excel file for you to see the format....

For opening following is the code

=(Sum({$<FinancialYear=,PostingDate ={"<$(=(Min(PostingDate)))"}>}  Cost_Amt_Actual)+

Sum({$<FinancialYear=,PostingDate ={"<$(=(Min(PostingDate)))"}>}  Cost_Amt_Expected))/100000

for closing

=(Sum({$<FinancialYear=,PostingDate ={"<$(=(Max(PostingDate)))"}>}  Cost_Amt_Actual)+

Sum({$<FinancialYear=,PostingDate ={"<$(=(Max(PostingDate)))"}>}  Cost_Amt_Expected))/100000

Thanks and Regards,

Rohit

[#9]

HI Rohit,

As same like previous qliker told.. Set analysis won't work based on dimenion..

You can solve by 2 method..

1. Write simple if condition for Opening column (Only for opening)

2. Data model itself get separate table for Item wise, Location wise, Month wise Opening balance..

2nd method is the best solution for performance.. If you have less no of data you can go with firs solution    

Karthik

[tresesco]

If you put <FinancialYear=> in set, that means your Year selection is void. Do you mean that(If not, try removing this)? It would be easier to help if you can share your sample application and a clear expected result.

Thanks.