Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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.
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))
You wait weeks for an AGGR question … then 2 come along at once 😀.
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!