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: 
stevietm
Creator
Creator

Aggr if statement in expression

Hi All

I have a trans table that has users with an amount of airtime that has been used. I split this by month and the user groups. Now i want to count in a expression the distinct amount of users that used more than R30 for a month.

So i have a pivot table with my dimensions as Report Group (the group/ customer name) and the month the usage of airtime took place. So i want to show for each month how many users have used more than R30.

See below expression i am using but is not working.


=IF(AGGR(sum(AIRTIME_AMOUNT) > 'R30.00',USER_ID) , Count(DISTINCT(USER_ID)))

Please note i am not using USER_ID as a dimensions (Too much information 3mil + lines) as i only want a total count of users by month and group. Not sure if there is a way to do it.


Thanks


Regards

STM

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this instead.

Count({<USER_ID = {"=Sum(AIRTIME_AMOUNT) > 30"}>} Distinct USER_ID)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

7 Replies
Anil_Babu_Samineni

Try this?

Count(Aggr(Count(If(Sum(AIRTIME_AMOUNT) > 'R30.00', USER_ID) DISTINCT USER_ID), USER_ID))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
stevietm
Creator
Creator
Author

It gives error in expression, I try to change it but i don,t get a value back

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Try this instead.

Count({<USER_ID = {"=Sum(AIRTIME_AMOUNT) > 30"}>} Distinct USER_ID)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
devarasu07
Master II
Master II

Hi,

can u try like this?

=Sum(Aggr(If(sum(AIRTIME_AMOUNT)>'R30.00',USER_ID), Count(DISTINCT(USER_ID))))

is your AIRTIME_AMOUNT >'R30.00' correct condition ? if it's numeric just try >30

Thanks,

Deva

Anil_Babu_Samineni

Does R30.00 returns Flag, Can you check using Filter

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mkamal
Contributor III
Contributor III

This formula worked for one of my apps.  there is one issue.  If I click on one of the cost center values in the table.    the  charttable's contents become blank

Formula I used:

=Count({$<[Employee] = {"=
(
Round(
Sum(
Aggr(
(sum([Employee FTE Calculation] )/(($(vCountOfDays)*(40/7)))
)
,[Employee]
)
)
, 0.01 )

) >$(vFTE_Threshold)"}>} Distinct [Employee])

 

The chart table looks like this:

Cost Center|  Above Expression

100000                |    8

20000                 |   2

 

If I click on one of the cost center values in the table.    the table then looks like this 

 

Cost Center |  Above Expression

-                         |   0

 

 

The dimension Cost center lives in the main table of the data model, how the "above expression" is in a table that links to the main table.

any idea on how can I correct this?

 

 

 

 

 

     

 

 

 

   

 

 

 

mkamal
Contributor III
Contributor III

a colleague at work was able to help me .     My formula was working but the inner FTE calc was factoring in the cost center and thus the result of that calc was less than my $(FTE_Threshold) variable.

I had to add this {<[CostCenter]=>}  to my inner sum, so that the calculation ignores the cost center in the calculation and when it evaluated if the result is less than my FTE threshold variable .


=


Count({$<[Employee] = {"=
(
Round(
Sum(
Aggr(
(sum({<[CostCenter]=>}[EmployeeTimeMetrics] )/(($(vCountOfDays)*(40/7)))
)
,[Employee]
)
)
, 0.01 )

) >$(FTE_Threshhold_Variable)"}>} Distinct [Employee])

Now everything works great.