Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Sum of count distinct value &amp; exception some value

Hi , I have table content with room number(A) , num of persons(B) , extra person(c) ,extra charge(D) , adults/ child(E) columns. In this if extra person is child there will be no extra charge equal to 0 and 0 charg for entire room mem. I need sum of distinct extra charge column.

A    B   C      D        E

20   3    1   100     Child

Total           200

But I need result following

A    B   C      D        E

20   3    1    0     Child

Total           100

Labels (3)

• ### QlikWorld 2023

1 Solution

Accepted Solutions
Contributor II
Author

Thanks for all ....but ...I didn't get the solution as suggested.

But I have got solution by myself.

• Have created another column with child charges 100 with respect to Room number and then I have subracted new column from D column....then I got result ....
5 Replies
Creator III

You can create a new column using Qlik's script editor to achieve this.

YourTableName:
A,
B,
C,
D,
E,
If(E = 'Child', 0, D) as NewExtraCharge

NewTableName:
A,
B,
C,
Sum(NewExtraCharge) as SumDistinctExtraCharge
Resident YourTable
Group By A, B, C, E;

DROP Table YourTableName;

Contributor II
Author

I need the solution in chart expression level , could you pls help me with that ?

Room number contains adult and child , if child is there charges zero ...I need to present zero value for adult with in the same room number

MVP

Hi ,

Try like below

Dim: A, B, C, E

Exp: If(SubStringCount(Concat(Distinct Total<A> E, ','), 'Child')=1, 0, D)

Thanks & Regards, Mayil Vahanan R
Creator III

Try this in Chart level
Assuming you have a chart where you want to show the sum of distinct extra charges