topic Re: To find unique charges based on ID,REGION. in QlikView

To find unique charges based on ID,REGION.

hamzabob1 — Mon, 07 Sep 2015 12:28:16 GMT

Hello all,

I have one problem can u please help me out.

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350

I need to calculate sum of VALUE w.r.t ID.

EX: ID = B1

VALUE = 550

As per the scenario the TAX will become 150, where as I need to consider it as unique(i.e., only 50 because my ID is B1 only)

so the final result should display as follows

________________________

ID: B1

Transaction count: 3

Region: ABC

TAX: 50

VALUE: 550

___________________________And also require Region wise as below

Region: ABC

TAX: 50

VALUE: 550

Region: XYZ

TAX: 25

Value:1250

_____________________________

Thanks in advance

Re: To find unique charges based on ID,REGION.

sunny_talwar — Mon, 07 Sep 2015 12:34:01 GMT

‌for 1st requirement:

Dimension ID

Expression

1) Count(ID)

2) Only(REGION)

3) Only(TAX) or Sum(Distinct TAX)

4) Sum(VALUE)

Re: To find unique charges based on ID,REGION.

maxgro — Mon, 07 Sep 2015 12:35:19 GMT

maybe

sum(VALUE) / avg(TOTAL <ID> TAX)

Re: To find unique charges based on ID,REGION.

sunny_talwar — Mon, 07 Sep 2015 12:35:59 GMT

‌For second requirement:

Dimension: REGION

Expressions

1) Only(Tax) or Sum(Distinct TAX)

2) Sum(VALUE)

Re: To find unique charges based on ID,REGION.

Anonymous — Mon, 07 Sep 2015 12:49:52 GMT

are u looking for this ?

ID	=Only(Tax)	=Sum({<ID={'B1'}>}Value)	=count(Transaction)	=Only({<ID={'B1'}>}Region)
		550	7	ABC
B1	50	550	3	ABC
B2	25	0	2
B3	30	0	2

Re: To find unique charges based on ID,REGION.

kavita25 — Mon, 07 Sep 2015 12:56:18 GMT

PFA..

Regards,

Kavita

Re: To find unique charges based on ID,REGION.

hamzabob1 — Tue, 08 Sep 2015 05:45:50 GMT

Thanks everyone its working fine...

thanks again.

Re: To find unique charges based on ID,REGION.

hamzabob1 — Tue, 08 Sep 2015 06:19:21 GMT

Now i have one more ID with the same TAX like 30,50...

and i want to show in text box..

thanks in advance...

Re: To find unique charges based on ID,REGION.

Anonymous — Tue, 08 Sep 2015 06:29:17 GMT

what is your expected output?

Re: To find unique charges based on ID,REGION.

kavita25 — Tue, 08 Sep 2015 06:36:05 GMT

Please elaborate ur requirement...with the expected result

Re: To find unique charges based on ID,REGION.

hamzabob1 — Tue, 08 Sep 2015 06:38:12 GMT

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350
B4	T8	PQ	30	300
B4	T9	PQ	30	350

Above is my new table now.and my expected output is:

sum(TAX)=135

Re: To find unique charges based on ID,REGION.

kavita25 — Tue, 08 Sep 2015 06:46:45 GMT

You want this with addition to the above result?? or what??

Re: To find unique charges based on ID,REGION.

Anonymous — Tue, 08 Sep 2015 06:51:07 GMT

Hi ,

Please elaborate what is the logic for sum(Tax) = 135 ??

Re: To find unique charges based on ID,REGION.

sunny_talwar — Tue, 08 Sep 2015 06:51:17 GMT

Try this in a text box object:

=Sum(Aggr(Avg(TAX), ID))

Re: To find unique charges based on ID,REGION.

hamzabob1 — Tue, 08 Sep 2015 06:52:31 GMT

SUM(TAX)(50+25+30+30)=135

Re: To find unique charges based on ID,REGION.

kavita25 — Tue, 08 Sep 2015 06:55:18 GMT

Try this expression:

=sum(aggr(sum(distinct TAX),ID))

Re: To find unique charges based on ID,REGION.

sunny_talwar — Tue, 08 Sep 2015 06:56:12 GMT

See the attached QVW:

Re: To find unique charges based on ID,REGION.

hamzabob1 — Tue, 08 Sep 2015 07:01:06 GMT

Thank you Sunny & Kavita that is wat i am looking for..

thanks again

Re: To find unique charges based on ID,REGION.

Tue, 08 Sep 2015 07:17:38 GMT

In straight table:

Dimension : ID,REGION,TAX

Expression1: Aggr(Count([TRANSACTION NO]),REGION)

Expression 2: Aggr(DISTINCT Sum(VALUE),REGION)

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350
B4	T8	PQ	30	300
B4	T9	PQ	30	350

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350
B4	T8	PQ	30	300
B4	T9	PQ	30	350

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350
B4	T8	PQ	30	300
B4	T9	PQ	30	350