Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
vidyasagar159
Creator II
Creator II

Dimension Expression with aggregation grouping

Hi All,

Hope you guys are doing fantastic. I have a simple requirement. Below is the simple Dimension Expression I want to add it to the 100% stacked Bar chart. Unfortunately, I am not getting the right results. Is something wrong with the expression?

=if(Aggr(Sum(FUNDBALANCE),COMPANY,PLANYEAR)>500000000 ,'Greater Than $500M',

if(Aggr(Sum(FUNDBALANCE),COMPANY,PLANYEAR)<50000000 ,'Less Than $50M',

if(Aggr(Sum(FUNDBALANCE),COMPANY,PLANYEAR)>=200000000 and Aggr(Sum(FUNDBALANCE),COMPANY,PLANYEAR)<500000000 ,'$200-$500M',

if(Aggr(Sum(FUNDBALANCE),COMPANY,PLANYEAR)>=50000000 and Aggr(Sum(FUNDBALANCE),COMPANY,PLANYEAR)<200000000 ,'$50-$200M'))))

Please find the attached image for reference. This logic is working fine in other BI tools like Tibco Spotfire but not sure how to build this one in Qlik Sense. A help would be much appreciated.

BarChart.PNG

Thanks,

-Vidya

1 Solution

Accepted Solutions
vidyasagar159
Creator II
Creator II
Author

Yes. Anil I was able to fix that issue.

Resolution:

I created the Plan Size dimension in script editor using a resident table.

In Front End

Dimension 1: PLAN YEAR

Dimension 2:  PLANSIZE (This dimension I have created in Script editor as I mentioned)

Measure Expression: sum(FUNDBALANCE) / sum(total <PLANYEAR> FUNDBALANCE)

Result:


100%Stacked Bars-Working.PNG

Thanks,

-Vidya

View solution in original post

9 Replies
Anil_Babu_Samineni

"I am not getting the right results"

So, This really tough to explain without having Data?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vidyasagar159
Creator II
Creator II
Author

Hi All,

Please find the sample data below.

   

Plan YearCompanyFund Balance
2010Apple500000000
2010MS50000000
2010Nokia3500000
2010Samsung200000
2010Motorolla620000000
2010Sony300000000
2011Motorolla500000000
2011Sony50000000
2011Samsung3500000
2011Nokia200000
2011Apple620000000
2011MS300000000
2012MS500000000
2012Apple50000000
2012Samsung3500000
2012Nokia200000
2012Sony620000000
2012Motorolla300000000
2013Apple500000000
2013MS50000000
2013Motorolla3500000
2013Sony200000
2013Samsung620000000
2013Nokia300000000
Anil_Babu_Samineni

Looks okay?

Capture.JPG

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vidyasagar159
Creator II
Creator II
Author

Hi Anil,

Thanks for looking into this. But I am using QLIK Sense Not a Qlik View and I believe there is no much difference these two products. The attached bar chart of yours is not a Stacked BAR. Actually, I wanted to see % split of the buckets for each year. Can you please check my attached bar chart once?. Can that be achievable in QLIK?

Thanks,

-Vidya

Anil_Babu_Samineni

Not  this?Capture.PNG

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vidyasagar159
Creator II
Creator II
Author

No Anil. When i convert this to 100% bar char i am getting 300% for every plan year. Acatully i want to split the dimensions for 100% per year

Example: For Plan Year 2013- 100% Stacked bar= 30% of '$200M-$500M', 20% of 'Greater than $500M', 40% of 'Less than $50M' and 10% of '%50M to $200M'

Thanks,

-Vidya

Anil_Babu_Samineni

Do you enable the relative future? Can u share updated one

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vidyasagar159
Creator II
Creator II
Author

Yes. Anil I was able to fix that issue.

Resolution:

I created the Plan Size dimension in script editor using a resident table.

In Front End

Dimension 1: PLAN YEAR

Dimension 2:  PLANSIZE (This dimension I have created in Script editor as I mentioned)

Measure Expression: sum(FUNDBALANCE) / sum(total <PLANYEAR> FUNDBALANCE)

Result:


100%Stacked Bars-Working.PNG

Thanks,

-Vidya

Anil_Babu_Samineni

Thats great, if you feel to mark helpful others. That would be dispense more information

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful