topic Re: Sum of count distinct value & exception some value in App Development

Sum of count distinct value & exception some value

sivavasam — Fri, 15 Nov 2024 21:15:37 GMT

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 Adult

20 3 1 100 Child

30 3 1 100 Adult

Total 200

But I need result following

A B C D E

20 3 1 0 Adult

20 3 1 0 Child

30 3 1 100 Adult

Total 100

Re: Sum of count distinct value & exception some value

Aasir — Sat, 11 Nov 2023 00:55:51 GMT

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

YourTableName:
LOAD
A,
B,
C,
D,
E,
If(E = 'Child', 0, D) as NewExtraCharge
FROM [Your Data Source];

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

DROP Table YourTableName;

Re: Sum of count distinct value & exception some value

sivavasam — Sat, 11 Nov 2023 19:57:27 GMT

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

Re: Sum of count distinct value & exception some value

MayilVahanan — Tue, 14 Nov 2023 05:53:32 GMT

Hi ,

Try like below

Dim: A, B, C, E

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

Re: Sum of count distinct value & exception some value

Aasir — Tue, 14 Nov 2023 11:31:21 GMT

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

Sum({<E={'Adult'}>} D) + Sum({<E={'Child'}>} 0)

Replace 'D' and 'E' with the actual column names representing extra charges and adults/child.

Re: Sum of count distinct value & exception some value

sivavasam — Thu, 16 Nov 2023 19:51:40 GMT

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 ....

topic Re: Sum of count distinct value &amp; exception some value in App Development

Sum of count distinct value & exception some value

Re: Sum of count distinct value & exception some value

Re: Sum of count distinct value & exception some value

Re: Sum of count distinct value & exception some value

Re: Sum of count distinct value & exception some value

Re: Sum of count distinct value & exception some value

topic Re: Sum of count distinct value & exception some value in App Development