Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
sureshbaabu
Contributor III

Calculated dimension(Dynamic) based on selections

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

5 Replies
Partner
Partner

Re: Calculated dimension(Dynamic) based on selections

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
Contributor III

Re: Calculated dimension(Dynamic) based on selections

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.


Re: Calculated dimension(Dynamic) based on selections

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

MVP & Luminary
MVP & Luminary

Re: Calculated dimension(Dynamic) based on selections

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
Valued Contributor II

Re: Calculated dimension(Dynamic) based on selections

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