Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sagarjagga
Creator
Creator

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

 

 

3 Solutions

Accepted Solutions
sunny_talwar

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

View solution in original post

sagarjagga
Creator
Creator
Author

sunny,

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

View solution in original post

sunny_talwar

Sort the dimension using this expression

Match(Only(Profile), 'Student', 'Teacher', 'Principal')

View solution in original post

6 Replies
tresesco
MVP
MVP

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

sunny_talwar

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
Creator
Creator
Author

sunny,

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

sunny_talwar

Sort the dimension using this expression

Match(Only(Profile), 'Student', 'Teacher', 'Principal')
sagarjagga
Creator
Creator
Author

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

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

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