topic Can Not Summarize Set Expression in Text Object. in QlikView

Can Not Summarize Set Expression in Text Object.

FsQvLearner — Sat, 16 Nov 2024 20:41:46 GMT

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.

Re: Can Not Summarize Set Expression in Text Object.

chrismarlow — Mon, 10 Jun 2019 18:43:26 GMT

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.

Re: Can Not Summarize Set Expression in Text Object.

sunny_talwar — Mon, 10 Jun 2019 19:06:46 GMT

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))

Re: Can Not Summarize Set Expression in Text Object.

chrismarlow — Mon, 10 Jun 2019 19:33:17 GMT

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

Re: Can Not Summarize Set Expression in Text Object.

FsQvLearner — Mon, 10 Jun 2019 19:41:33 GMT

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!