Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
FsQvLearner
Contributor
Contributor

Can Not Summarize Set Expression in Text Object.

Hello,

I am new to QV and I am trying to build a Text Object summary and I am not able to build the object as expected. 

 

Requirement:

Sum the total of the Divisions in a Text object to show percentage of Divisions that have shown growth in the Grade and Gender category.

 

=IF(NUM(SUM({$<Add_dt = {"$(vEndYear)*"},Division_Name={"*"},Compensation_Grade= {$(=vGr0_3)}>} Gender_Female_CNT),)
/NUM(SUM({$<Add_dt = {"$(vEndYear)*"},Division_Name={"*"},Compensation_Grade= {$(=vGr0_3)}>} CNT),)
- NUM(SUM({$<Add_dt = {"$(vStartYear)*"},Division_Name={"*"},Compensation_Grade= {$(=vGr0_3)}>} Gender_Female_CNT),)
/NUM(SUM({$<Add_dt = {"$(vStartYear)*"},Division_Name={"*"},Compensation_Grade= {$(=vGr0_3)}>} CNT))
>0.00,1,0)

 

Current Results:

In the Text Object, only “1” is returned and not the summarized results for all Divisions that have met the condition.

 

Expected / Hope to have:

Total divisions that meet condition, so I can then determine the percentage of divisions that meet the condition and surface in a Text object are a percent.

 

Best and thanks for suggestions.

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

May be this

=Sum(Aggr(
If(Sum({$<Add_dt = {"$(vEndYear)*"}, Division_Name = {"*"}, Compensation_Grade = {$(=vGr0_3)}>} Gender_Female_CNT)
/Sum({$<Add_dt = {"$(vEndYear)*"}, Division_Name = {"*"}, Compensation_Grade = {$(=vGr0_3)}>} CNT)
- Sum({$<Add_dt = {"$(vStartYear)*"}, Division_Name = {"*"}, Compensation_Grade = {$(=vGr0_3)}>} Gender_Female_CNT)
/Sum({$<Add_dt = {"$(vStartYear)*"}, Division_Name = {"*"}, Compensation_Grade = {$(=vGr0_3)}>} CNT)
> 0.00, 1, 0)
, Division))

View solution in original post

4 Replies
chrismarlow
Specialist II
Specialist II

Hi,

Can you clarify what you are looking for? When I first read it looked like you wanted a list of the divisions that meet the criteria in a text box, which would be odd.

Is it that you want a count of the instances that meet the criteria?

If so likely you are going to need to use aggr - something like sum(aggr(YOUR FUNCTION, Division_Name)), but your function may need a bit of tweaking.

Cheers,

Chris.

sunny_talwar

May be this

=Sum(Aggr(
If(Sum({$<Add_dt = {"$(vEndYear)*"}, Division_Name = {"*"}, Compensation_Grade = {$(=vGr0_3)}>} Gender_Female_CNT)
/Sum({$<Add_dt = {"$(vEndYear)*"}, Division_Name = {"*"}, Compensation_Grade = {$(=vGr0_3)}>} CNT)
- Sum({$<Add_dt = {"$(vStartYear)*"}, Division_Name = {"*"}, Compensation_Grade = {$(=vGr0_3)}>} Gender_Female_CNT)
/Sum({$<Add_dt = {"$(vStartYear)*"}, Division_Name = {"*"}, Compensation_Grade = {$(=vGr0_3)}>} CNT)
> 0.00, 1, 0)
, Division))
chrismarlow
Specialist II
Specialist II

You wait weeks for an AGGR question … then 2 come along at once 😀.

FsQvLearner
Contributor
Contributor
Author

Thank you  BOTH!  I tried various version of the set expression with the "aggr" function but to no avail!  The version that Sunny provided, is working as expected! I am 100% positive I will have some more expression questions in the future 😉

 

Best!