Calculated dimension(Dynamic) based on selections

[sureshbaabu]

Hello,

I have an issue when trying to use an calculated dimension that changes based on a selections.

It does not work only when you have calculated dimension on the ‘else’ part.

Column name: View_By

Values: ‘By Employee’, ’By Salary’

The following dimensions will change based on the selection on ‘View_By’ column

Dimension1 -> show top 15 COMPNAME based on count of Employee

if(COMPNAME<>'Nike', aggr(if(rank(count({<A_COUNT={"1"}>}Employee)) <=15,COMPNAME),COMPNAME))

Dimension2 -> top 15 COMPNAME based on sum of Salary

=if(COMPNAME<>'Nike', aggr(if(rank(sum({<A_COUNT={"1"}>}Salary)) <=15,COMPNAME),COMPNAME))

Current Dimension on Chart:

=if(GetFieldSelections(View_By)='By Employee’,
(if(COMPNAME<>'Nike', aggr(if(rank(count({<A_COUNT={"1"}>}Employee)) <=15,COMPNAME),COMPNAME))),
(if(COMPNAME<>'Nike', aggr(if(rank(sum({<A_COUNT={"1"}>}Salary)) <=15,COMPNAME),COMPNAME)))
   )

Issue: The results appear only when the I make a selection as ‘By Employee’ on View_By  column. But, when I choose ’By Salary’ – It get the message ‘No data to display’

Note:

1. Both the dimensions works when used on separately (Both gives data)
2. Dimensions works when you replace ‘else’ portion with a Column name (instead of a calculated dimension)
3. It dint work even after writing another ‘If’ for the else portion
4. I cannot replace charts based on conditions
5. Variables also dint help

Could you please share your suggestion on this issue? Please let me know if you need more clarity on the issue

Thank you,

Suresh

[phaneendra_kunc]

Is that possible to share your QVW?

for short term i would suggest to leverage "Conditional" show/hide on the dimension, assuming you are using QV 11. Actually this is a god way to maintain this.

So in here you will create two calculated dimensions. one for Employee and other for Salary.

1. if(COMPNAME<>'Nike', aggr(if(rank(count({<A_COUNT={"1"}>}Employee)) <=15,COMPNAME),COMPNAME))

Condition : View_By='By Employee’

2. if(COMPNAME<>'Nike', aggr(if(rank(count({<A_COUNT={"1"}>}Salary)) <=15,COMPNAME),COMPNAME))

Condition : View_By='By Salary’

[sureshbaabu]

Thank you for Responding.  I'm sorry; due to security reasons , I cannot share the QVW.

You are right. I have gone with your fix for now.

I will wait to see If someone else have other suggestions.

[avinashelite]

As per my analysis , their is not issue with the calculated dimension . I think its the issue with selection on View_by

try to making it a single select value and then check

[jagan]

Hi,

Try this expression

=if(Only(View_By)='By Employee’,

aggr(if(rank(count({<COMPNAME -={'Nike'}, A_COUNT={"1"}>}Employee)) <=15,COMPNAME), COMPNAME)

aggr(if(rank(sum({<COMPNAME -={'Nike'}, A_COUNT={"1"}>}Salary)) <=15,COMPNAME),COMPNAME)

   )

I think what phaneendra.kunche suggested is the best solution, you will have better control over the dimensions.  Infact without calculated dimensions and using multiple charts you can do this using Dimension limits. Just show and hide the charts based on the Selected value in View_by.  Calculated dimensions will have performance issues sometimes.

Regards,

Jagan.

[paulyeo11]

Hi All

Sorry i jump in here. Hope you guy are okay. as i try to get in touch with Jagan

Hi Jagan

I am Paul , I believe you know me , because you have help me settle many QV issue in the past.

Most of the time i though you are working for Qlik , Suddenly when i read a blog post , and mentioned the top contributor in forum , and you are one of them , and in your profile , it mentioned you work in singapore and since you are living and working in singapore , and most of the time i spend my time at indonesia , and happen that this week i am not travel , and will be stay at singapore , so i like to invite you for evening chat and buy you some dinner.

Hope that you can let me know when are you free ? and let me know where you like me to meet you ? i waiting for your reply soon. kindly send me a SMS to my HP 93261804

Paul Yeo

TDS