Qlik Community

Qlik Sense App Development

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

Highlighted
sagarjagga
New Contributor III

Sum of amount after a certain values of a field

Hi All,

I have a below data set 

NameProfilesales
AStudent10
BStudent50
CStudent60
ETeacher15
DTeacher35
FPrincipal10
GPrincipal10
HPrincipal20
JPrincipal20

my final chart should display net amounts after certain group . for example , below will be my final chart

Namesales
A10
B50
C60
Net Student sales120
E15
D35
Net Teacher Sales50
F10
G10
H20
J20
Net Principal Sales60

 

Any extension for this , will also work

 

 

Tags (1)
Labels (1)
3 Solutions

Accepted Solutions

Re: Sum of amount after a certain values of a field

One way to do this is to create an island table like this

Table:
LOAD * INLINE [
    Name, Profile, sales
    A, Student, 10
    B, Student, 50
    C, Student, 60
    E, Teacher, 15
    D, Teacher, 35
    F, Principal, 10
    G, Principal, 10
    H, Principal, 20
    J, Principal, 20
];

Dim:
LOAD * INLINE [
    Dim
    1
    2
];

and then a chart like this

Dimension

=Pick(Dim, Name, 'Net ' & Profile & ' Sales')

Expression

Sum(sales)

image.png

sagarjagga
New Contributor III

Re: Sum of amount after a certain values of a field

sunny,

How you have sorted the table. for me net value columns are coming at end.

Re: Sum of amount after a certain values of a field

Sort the dimension using this expression

Match(Only(Profile), 'Student', 'Teacher', 'Principal')
6 Replies
MVP
MVP

Re: Sum of amount after a certain values of a field

Have a look here: How-IntervalMatch-Solved-My-Profit-and-Loss-Dilemma  if it helps.

Re: Sum of amount after a certain values of a field

One way to do this is to create an island table like this

Table:
LOAD * INLINE [
    Name, Profile, sales
    A, Student, 10
    B, Student, 50
    C, Student, 60
    E, Teacher, 15
    D, Teacher, 35
    F, Principal, 10
    G, Principal, 10
    H, Principal, 20
    J, Principal, 20
];

Dim:
LOAD * INLINE [
    Dim
    1
    2
];

and then a chart like this

Dimension

=Pick(Dim, Name, 'Net ' & Profile & ' Sales')

Expression

Sum(sales)

image.png

sagarjagga
New Contributor III

Re: Sum of amount after a certain values of a field

sunny,

How you have sorted the table. for me net value columns are coming at end.

Re: Sum of amount after a certain values of a field

Sort the dimension using this expression

Match(Only(Profile), 'Student', 'Teacher', 'Principal')
sagarjagga
New Contributor III

Re: Sum of amount after a certain values of a field

Smart solution!!!
is there any way to distinguish the Net fields from original fields (by making bold the Net fields or by coloring)

Re: Sum of amount after a certain values of a field

Sure, you should be able to use Dim field for this...

If(Dim = 2, '<b>')