Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..??
You should be able to create this kind of view using a calculated dimension
Something like this
Dimension
Aggr(
If(ShipWeight < 250, Dual('Under250g', 1),
If(ShipWeight < 500, Dual('C250to500g', 2),
....
, [Order GuID])
Expression
Count(DISTINCT [Order GuID])
Hi sunny,
I have done it in straight table. what is recommended and where to dimension and expression . Please explain.
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?
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 ?
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
This is my chart, under dimension where shall I write that.
If you are looking to get this
You need to create a new chart with just 1 dimension and 1 expression
yes