topic Re: Calculated dimension expression. in QlikView

Calculated dimension expression.

rohitians — Wed, 12 Jun 2013 04:04:13 GMT

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

Re: Calculated dimension expression.

sujeetsingh — Wed, 12 Jun 2013 04:21:50 GMT

try using =(your expression) as calculated dimension

Re: Calculated dimension expression.

rohitians — Wed, 12 Jun 2013 04:23:33 GMT

Hello SOS,

I tried its showing error in calculation...

Thanks and Regards,

Rohit

Re: Calculated dimension expression.

Wed, 12 Jun 2013 04:28:36 GMT

Please post sample application..

Re: Calculated dimension expression.

tresB — Wed, 12 Jun 2013 04:49:56 GMT

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.

Re: Calculated dimension expression.

Wed, 12 Jun 2013 04:52:51 GMT

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

Re: Calculated dimension expression.

rohitians — Wed, 12 Jun 2013 05:37:53 GMT

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

Re: Calculated dimension expression.

rohitians — Wed, 12 Jun 2013 05:38:57 GMT

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

Re: Calculated dimension expression.

Wed, 12 Jun 2013 05:55:00 GMT

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

Re: Calculated dimension expression.

tresB — Wed, 12 Jun 2013 05:56:17 GMT

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.