Aggr if statement in expression

[stevietm]

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

[Anil_Babu_Samineni]

Try this?

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

[stevietm]

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

[kaushiknsolanki · Accepted solution]

Try this instead.

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

Regards,

Kaushik Solanki

[devarasu07]

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

[mkamal]

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]

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.