Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
agni_gold
Specialist III
Specialist III

Need Help on Calculated Dimension

Hi all ,

I have a situation ,

I have made a one pivot table it seems like

   

AccountValue
A 10
B20
C30
D40
E50
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.

7 Replies
MK_QSL
MVP
MVP

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

sunny_talwar

Are you looking to do this:

Capture.PNG

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

avinashelite

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

agni_gold
Specialist III
Specialist III
Author

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)

MK_QSL
MVP
MVP

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

agni_gold
Specialist III
Specialist III
Author

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 .

agni_gold
Specialist III
Specialist III
Author

PIc.jpg

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))