Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
harikattamudi
Contributor
Contributor

QlikSense AGGR Function

Dear experts,

I’m looking for an aggregate function in Qlik sense to do the below

My data table looks like below.

Part

Plan

Actual

Percentage(Caluclated as Actual/ Plan)

A

3

3

100%

B

3

6

200%

C

4

4

100%

D

5

0

0%

E

0

2

#DIV/0!

F

0

0

#DIV/0!

G

4

3

75%

H

2

0

0%

I

0

3

#DIV/0!

I want the output as a bar graph with the data as shown below

Percentage

Count

0%

2

75%

1

100%

2

200%

1

>100%

2

N/A

1

Please see the colors from the input table and out table for a clear understanding of the data.

Please note that when the plan is 0 and actual is >0, I want the Percentage to show as > 100% with count as number of records (in this case it is 2).

If both plan and actual are 0. I want the Percentage to be N/A or something unique. So, it can be treated as no plan and no actual (In this case we have only 1 record).

Labels (1)
1 Reply
sunny_talwar

May be this

Dimension

Aggr(

If(Plan = 0 and Actual = 0, 'N/A',

If(Plan = 0, '>100%',

If(Actual = 0, '0%',

If(Actual/Plan <= 0.75, '75%',

If(Actual/Plan <= 1, '100%'

If(Actual/Plac <= 2, '200%'))))))

,Part)

Expression

Count(DISTINCT Part)