Can Not Summarize Set Expression in Text Object.

[FsQvLearner]

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.

[chrismarlow]

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 · Accepted solution]

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]

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

[FsQvLearner]

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!