Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

stevietm
New Contributor III

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

Tags (1)
1 Solution

Accepted Solutions

Re: Aggr if statement in expression

Try this instead.

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

Regards,

Kaushik Solanki

7 Replies

Re: Aggr if statement in expression

Try this?

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

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
New Contributor III

Re: Aggr if statement in expression

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

Re: Aggr if statement in expression

Try this instead.

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

Regards,

Kaushik Solanki

devarasu07
Honored Contributor II

Re: Aggr if statement in expression

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

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

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)
Highlighted
mkamal
New Contributor

Re: Aggr if statement in expression

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
New Contributor

Re: Aggr if statement in expression

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.