topic Re: Calculated dimension(Dynamic) based on selections in QlikView

Calculated dimension(Dynamic) based on selections

sureshbaabu — Tue, 14 Jul 2015 09:55:56 GMT

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:

Both the dimensions works when used on separately (Both gives data)
Dimensions works when you replace ‘else’ portion with a Column name (instead of a calculated dimension)
It dint work even after writing another ‘If’ for the else portion
I cannot replace charts based on conditions
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

Re: Calculated dimension(Dynamic) based on selections

phaneendra_kunc — Tue, 14 Jul 2015 10:04:51 GMT

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’

Re: Calculated dimension(Dynamic) based on selections

sureshbaabu — Tue, 14 Jul 2015 11:06:09 GMT

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

avinashelite — Tue, 14 Jul 2015 11:14:36 GMT

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

Re: Calculated dimension(Dynamic) based on selections

jagan — Tue, 14 Jul 2015 11:25:11 GMT

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.

Re: Calculated dimension(Dynamic) based on selections

paulyeo11 — Wed, 15 Jul 2015 02:10:20 GMT

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