Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

Try this instead.

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

7 Replies
Highlighted

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)
Highlighted
Creator
Creator

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

Highlighted
MVP & Luminary
MVP & Luminary

Try this instead.

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Highlighted
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

Highlighted

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
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?

 

 

 

 

 

     

 

 

 

   

 

 

 

Highlighted
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.