Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this instead.
Count({<USER_ID = {"=Sum(AIRTIME_AMOUNT) > 30"}>} Distinct USER_ID)
Regards,
Kaushik Solanki
Try this?
Count(Aggr(Count(If(Sum(AIRTIME_AMOUNT) > 'R30.00', USER_ID) DISTINCT USER_ID), USER_ID))
It gives error in expression, I try to change it but i don,t get a value back
Try this instead.
Count({<USER_ID = {"=Sum(AIRTIME_AMOUNT) > 30"}>} Distinct USER_ID)
Regards,
Kaushik Solanki
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
Does R30.00 returns Flag, Can you check using Filter
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?
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.