topic Re: Classify under weight category in QlikView

Classify under weight category

ngreddy1982 — Wed, 25 Nov 2020 16:16:04 GMT

I am trying to get a report looks some thing like this which is an example from bi manager.

Orders/Mth 2016	Under250g	C250to500g	C500to750g	c750gto1Kg	c1Kgto125Kg	c125Kgto1_5Kg	c1_5kgto175Kg	c175kgto2Kg	Over2kg
1	2,821	1,696	946	525	268	156	94	81	286
2	2,679	1,342	732	384	228	124	70	84	233
9	1,393	901	555	340	204	111	66	60	152
10	1,542	1,028	655	343	214	93	58	51	129
									I

I have used below query

Select

ih.[Order GuID],

sum(il.[Gross Weight]) as ShipWeight,

cn.[Actual Packaging], itm.[Description],

count(il.No_) as coutn

FROM [TBW_BI].[dbo].[dreams$Sales Invoice Line] as il

inner join [TBW_BI].[dbo].[dreams$Sales Invoice Header] as ih

on il.[Document No_]=ih.No_

inner join [TBW_BI].[dbo].[dreams$Consignment] as cn

on ih.[Consignment ID]=cn.[id]

left join [TBW_BI].[dbo].[dreams$Item] as itm

on cn.[Actual Packaging]=itm.[No_]

where ih.[Ship-to Country_Region Code]='HK'

and ih.[Shipment Date]>='2016-10-01' and ih.[Shipment Date]<'2017-03-31'

group by ih.[Order GuID],itm.Description, cn.[Actual Packaging]

to get table like below

now how can I classify them or group them as per the request. Got to get how many orders which count column are under 250 grams , and how many are under 250 to 500 grams etc..??

Re: Classify under weight category

sunny_talwar — Wed, 05 Apr 2017 11:07:30 GMT

You should be able to create this kind of view using a calculated dimension

Buckets

Re: Classify under weight category

sunny_talwar — Wed, 05 Apr 2017 11:10:29 GMT

Something like this

Dimension

Aggr(

If(ShipWeight < 250, Dual('Under250g', 1),

If(ShipWeight < 500, Dual('C250to500g', 2),

....

, [Order GuID])

Expression

Count(DISTINCT [Order GuID])

Re: Classify under weight category

ngreddy1982 — Wed, 05 Apr 2017 11:16:41 GMT

Hi sunny,

I have done it in straight table. what is recommended and where to dimension and expression . Please explain.

Re: Classify under weight category

sunny_talwar — Wed, 05 Apr 2017 11:18:33 GMT

huh????

I have done it in straight table

You have done this in the straight table already? Great, so what do you mean by this?

what is recommended and where to dimension and expression . Please explain.

Explain what my friend?

Re: Classify under weight category

ngreddy1982 — Wed, 05 Apr 2017 12:12:48 GMT

Sorry if I am asking basics.. I mean, you said about dimension and expression, i am trying to figure out. shall i go to table properties and then to expression ?

Re: Classify under weight category

sunny_talwar — Wed, 05 Apr 2017 12:16:15 GMT

There is nothing wrong in asking the basics my friend. But you need to clearly state what you are asking.

Yup... on the dimensions tab of chart properties you need to add

Aggr(

If(ShipWeight < 250, Dual('Under250g', 1),

If(ShipWeight < 500, Dual('C250to500g', 2),

....

, [Order GuID])

Expression's tab

Count(DISTINCT [Order GuID])

I have not typed the whole dimension, you need to complete it by adding the remaining if statements. Would you be able to do this or do you need help with that? I am more than happy to help

Re: Classify under weight category

ngreddy1982 — Wed, 05 Apr 2017 12:26:00 GMT

This is my chart, under dimension where shall I write that.

Re: Classify under weight category

sunny_talwar — Wed, 05 Apr 2017 12:29:13 GMT

If you are looking to get this

You need to create a new chart with just 1 dimension and 1 expression

Re: Classify under weight category

ngreddy1982 — Wed, 05 Apr 2017 12:33:25 GMT

yes

Re: Classify under weight category

ngreddy1982 — Wed, 05 Apr 2017 12:37:20 GMT

Which dimension shall i use and what expression

Re: Classify under weight category

sunny_talwar — Wed, 05 Apr 2017 12:42:25 GMT

Dimension

Aggr(

If(ShipWeight < 250, Dual('Under250g', 1),

If(ShipWeight < 500, Dual('C250to500g', 2),

....

, [Order GuID])

Expression

Count(DISTINCT [Order GuID])

Re: Classify under weight category

ngreddy1982 — Wed, 05 Apr 2017 12:50:31 GMT

I have created new straight table took only orderguid to used dimensions and under add calculated dimension i am using this. correct?

Re: Classify under weight category

sunny_talwar — Wed, 05 Apr 2017 12:58:15 GMT

Do you see the unclosed parenthesis for each of the if? You need to close them Giridhar

Re: Classify under weight category

ngreddy1982 — Wed, 05 Apr 2017 13:12:09 GMT

How to close it ?

Re: Classify under weight category

sunny_talwar — Wed, 05 Apr 2017 13:15:47 GMT

Would you be able to share what you have typed in your dimension here? as text and not as an image

Re: Classify under weight category

ngreddy1982 — Wed, 05 Apr 2017 13:17:01 GMT

Aggr(

If(ShipWeight < 250, Dual('Under250g', 1),

If(ShipWeight < 500, Dual('250to500g', 2),

If(ShipWeight < 750, Dual('500gto750g', 3),

If(ShipWeight < 1000, Dual('750gto1kg', 4),

If(ShipWeight < 1250, Dual('1kgto1.25kg', 5),

If(ShipWeight < 1500, Dual('1.25kgto1.50kg', 6),

If(ShipWeight < 1750, Dual('1.50kgto1.75kg', 7),

If(ShipWeight < 2000, Dual('1.75kgto2kg', 8),

If(ShipWeight < 7000, Dual('over2kg', 9),

[Order GuID])

Re: Classify under weight category

sunny_talwar — Wed, 05 Apr 2017 13:50:17 GMT

Try this:

Aggr(

If(ShipWeight < 250, Dual('Under250g', 1),

If(ShipWeight < 500, Dual('250to500g', 2),

If(ShipWeight < 750, Dual('500gto750g', 3),

If(ShipWeight < 1000, Dual('750gto1kg', 4),

If(ShipWeight < 1250, Dual('1kgto1.25kg', 5),

If(ShipWeight < 1500, Dual('1.25kgto1.50kg', 6),

If(ShipWeight < 1750, Dual('1.50kgto1.75kg', 7),

If(ShipWeight < 2000, Dual('1.75kgto2kg', 8),

If(ShipWeight < 7000, Dual('over2kg', 9)))))))))),

[Order GuID])