Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ngreddy1982
Contributor II
Contributor II

Classify under weight category

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

          

Orders/Mth 2016Under250gC250to500gC500to750gc750gto1Kgc1Kgto125Kgc125Kgto1_5Kgc1_5kgto175Kgc175kgto2KgOver2kg
12,8211,6969465252681569481286
22,6791,3427323842281247084233
91,3939015553402041116660152
101,5421,028655343214935851129
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

Untitled1.png

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

17 Replies
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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

Untitled2.png

sunny_talwar

If you are looking to get this

Capture.PNG

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

ngreddy1982
Contributor II
Contributor II
Author

yes