Classify under weight category

[ngreddy1982]

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

[sunny_talwar]

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

Buckets

[sunny_talwar]

Something like this

Dimension

Aggr(

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

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

....

, [Order GuID])

Expression

Count(DISTINCT [Order GuID])

[ngreddy1982]

Hi sunny,

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

[sunny_talwar]

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?

[ngreddy1982]

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 ?

[sunny_talwar]

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

[ngreddy1982]

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

[sunny_talwar]

If you are looking to get this

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

[ngreddy1982]

yes