topic Re: Need Help on Calculated Dimension in QlikView

Need Help on Calculated Dimension

agni_gold — Mon, 13 Jul 2015 12:15:52 GMT

Hi all ,

I have a situation ,

I have made a one pivot table it seems like

Account	Value
A	10
B	20
C	30
D	40
E	50
F

Account is my one dimension and value is my second calculated dimension , which is calculated by

below calculated expressiob

=(round(aggr(sum(Value),Account),0.01))

But now I want to show in front of F is the division of E/A . I have tried many ways , please suggest me any answer.

Re: Need Help on Calculated Dimension

MK_QSL — Mon, 13 Jul 2015 12:19:29 GMT

=ROUND(Aggr(IF(Account = 'F', SUM(TOTAL {<Account = {'E'}>}Value)/SUM(TOTAL {<Account = {'A'}>}Value),SUM(Value)),Account),0.01)

Re: Need Help on Calculated Dimension

sunny_talwar — Mon, 13 Jul 2015 12:27:05 GMT

Are you looking to do this:

Try this expression as your calculated dimension:

=If(Account = 'F',

Num(Only(TOTAL {<Account = {'E'}>}Aggr(Sum({<Account = {'E'}>}Value), Account))/

Only(TOTAL {<Account = {'A'}>}Aggr(Sum({<Account = {'A'}>}Value), Account)), '#,##0.00'),

Aggr(Sum(Value), Account))

HTH

Best,

Sunny

Re: Need Help on Calculated Dimension

avinashelite — Mon, 13 Jul 2015 12:30:43 GMT

You want a static calculation or dynamic? i.e. you always wanted the division of A/E for the F??

Re: Need Help on Calculated Dimension

agni_gold — Mon, 13 Jul 2015 17:43:38 GMT

I have written following expression already , so can you please help me to inbuilt your expression with your logic.

I need this condition must in calculation if([Account Type]=[Entry Type]

=aggr(sum(if([Account Type]=[Entry Type],Value))/1000000,Account)

Re: Need Help on Calculated Dimension

MK_QSL — Mon, 13 Jul 2015 18:41:08 GMT

I am confused now... What you have asked now is that same of your original question? or it is something like 2nd question?

Re: Need Help on Calculated Dimension

agni_gold — Mon, 13 Jul 2015 18:59:59 GMT

Yes it is some kind of second question but related to this :

Now i have one expresion

sum(Value)/1000000

now i want to do same thingh in expression also , i have written same as in above answer but it is not working .

Re: Need Help on Calculated Dimension

agni_gold — Mon, 13 Jul 2015 19:09:59 GMT

Red square are my dimesion for which i have asked question , but after that i have added one expression

sum(Value)/1000000

and in this pivot table i have one dimesion region and i have draged to horizontaly , now i want to show the division as i asked before. For each region .

Nut i am applying the same expression as in above answer but it is not working . Please help.

I am using

=If(Account = '%Operating',

Sum({<Account = {'Income'},[Value] = {"=[Account Type]=[Entry Type]"}>}Value/1000000)

Sum({<[Account = {'Total'}, Value = {"=[Account Type]=[Entry Type]"}>}Value/1000000)

round(Sum({<Amount = {"=[Account Type]=[Entry Type]"}>}Value)/1000000,0.01))