Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Class function

Hi,

I want to create a dimension using class function like below for a bar chart.

My dimension is Customer Name

and measurement is Gross Profit = sum(revenue)-(sum(cost)+sum(additional services))

My interval will be 10000.

can you please help me with the syntax.

how i can put the calculation for Gross profit inside the expression, to see how many customers fall under 0 to 10000 or 10000 to 20000 gross profit bucket.

thanks

Prasanta

Tags (1)
9 Replies
MVP
MVP

Re: Class function

I guess something like this:

Dimension:

Class(Aggr((Sum(revenue)-(Sum(cost)+Sum(additional services))), [Customer Name]), 10000)

Expression:

Count(DISTINCT [Customer Name])

Not applicable

Re: Class function

this is not working in my case

MVP
MVP

Re: Class function

Would you be able to share some sample data?

MVP
MVP

Re: Class function

Seems to be working for me in the attached sample. Can you check to see if you have the same structure?

Capture.PNG

Sample script used:

Table:

LOAD *,

  Ceil(Rand() * (50000 - 25000) + 25000) as Revenue,

  Ceil(Rand() * (15000 - 10000) + 10000) as Cost,

  Ceil(Rand() * (10000 - 5000) + 5000) as [Additional Services]

While IterNo() <= 3;

LOAD * Inline [

Customer

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

];

Dimension:

=Class(Aggr(Sum(Revenue)-Sum(Cost) - Sum([Additional Services]), Customer), 10000)

Expression:

Count(DISTINCT Customer)

Not applicable

Re: Class function

CountryCustomersRevenueCost1Cost2Cost3
IndOrang1004307
RusBharti20005454534
IndRel1001234
RusHP500541135
AmericaOrang234345433335
EMEABharti34237555343
PakRel64576344
BangHP5477343435345
AmericaKelvinator1004543
EMEAwirlpool200068743354
PakOrang1002334
BangBharti5002423223
EMEARel6000464322

I want to create a class for based on Revenue and Profit.

and want to see customers at interval of 100.

1st Bar chart

=====================

My dimension is Customer Name

and measurement for Profit = sum(revenue)-(sum(cost1)+sum(cost2)+sum(cost3))

My interval will be 100.

2nd Bar chart

=======================

Dimension customer name

Measurement - Sum(rev)

Thanks

Prasata

Not applicable

Re: Class function

basically i want to see on bar chart, the customers with Gross profit interval of 0 to 100. 100 to 200 and so on.

Simillary want to see customers on bar chart for based on total revenu with interval from 0 to 100, 100 to 200 and so on.

MVP
MVP

Re: Class function

Interval will be 100?

What does that even mean? I have no idea how to interpret that. Can you draw a picture by hand to show what is that you are looking for?

Not applicable

Re: Class function

Actually on X axis of bar chart, i want to put the interval like, 0 to 100, 100 to 200, 200 to 300...and so on..

and on Y axis, when i sum up the revenue for a particular customer, i will have the bar under respective interval..

for example, sum of revenue for Orange is 2543, so the bar for orange should be drawn under interval 2500 to 2600.

simillary, i want to calculate the profit and draw the bar for customer under respective intervals.

is it possible?

thanks

MVP
MVP

Re: Class function

QlikSense isn't working right now, but this should work in Qlik Sense the same way:

CHART1

Dimension:

=Class(Aggr(Sum(Revenue), Customers), 100)

Expression:

=Count(DISTINCT Customers)

CHART2

Dimension:

=Class(Aggr(Sum(Revenue-Cost1-Cost2-Cost3), Customers), 100)

Expression:

=Count(DISTINCT Customers)

Capture.PNG