topic Re: Aggr if statement in expression in QlikView

Aggr if statement in expression

stevietm — Thu, 12 Oct 2017 07:51:14 GMT

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

Re: Aggr if statement in expression

Anil_Babu_Samineni — Thu, 12 Oct 2017 07:55:20 GMT

Try this?

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

Re: Aggr if statement in expression

stevietm — Thu, 12 Oct 2017 08:01:10 GMT

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

Re: Aggr if statement in expression

kaushiknsolanki — Thu, 12 Oct 2017 08:02:10 GMT

Try this instead.

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

Regards,

Kaushik Solanki

Re: Aggr if statement in expression

devarasu07 — Thu, 12 Oct 2017 08:03:11 GMT

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

Re: Aggr if statement in expression

Anil_Babu_Samineni — Thu, 12 Oct 2017 08:05:13 GMT

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

Re: Aggr if statement in expression

mkamal — Wed, 09 Jan 2019 15:38:42 GMT

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?

Re: Aggr if statement in expression

mkamal — Wed, 09 Jan 2019 19:23:50 GMT

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.