topic Re: Group by a dimension. in QlikView

Group by a dimension.

markgraham123 — Mon, 22 Jun 2015 16:40:26 GMT

Hi,

I was trying to group the data w.r.t the dimension Warehouse.

When a warehouse is selected , intstead of seeing all the records with a total in a table.

I wanna see only the total of the data in the columns.

Please look at the atatchment - Table "0-8 WOH"

Re: Group by a dimension.

sunny_talwar — Mon, 22 Jun 2015 16:57:17 GMT

Can you reduce the size of the document?

Re: Group by a dimension.

markgraham123 — Mon, 22 Jun 2015 17:08:15 GMT

Hi Sunny,

I tried to. I was not able to reduce the size.

Here is the screenshot for your reference.

I was trying to have only one column, which shows the total sum of SKUs, pallets, and units.

Instead of many columns.

Re: Group by a dimension.

markgraham123 — Mon, 22 Jun 2015 17:12:38 GMT

If((Sum([Qty On Hand])/([360-day Velocity]/52))>=0 and (Sum([Qty On Hand])/([360-day Velocity]/52))<=8, Count(Distinct(SKU#)))

If((Sum([Qty On Hand])/([360-day Velocity]/52))>=0 and (Sum([Qty On Hand])/([360-day Velocity]/52))<=8,Sum([Units Packed]))

If((Sum([Qty On Hand])/([360-day Velocity]/52))>=0 and (Sum([Qty On Hand])/([360-day Velocity]/52))<=8, Aggr(Count(LBCD),SKU#))

These are the exp. used

Re: Group by a dimension.

sunny_talwar — Mon, 22 Jun 2015 17:14:34 GMT

Great and is this the part you are looking for?

and nothing below it, right?

Re: Group by a dimension.

markgraham123 — Mon, 22 Jun 2015 17:15:23 GMT

Yes.

Exactly.

Re: Group by a dimension.

markgraham123 — Mon, 22 Jun 2015 17:15:48 GMT

With a warehouse name.

Re: Group by a dimension.

sunny_talwar — Mon, 22 Jun 2015 17:16:32 GMT

Try this:

If(Dimensionality() = 0, YourExpressions) for all the three expressions and see if that does the trick for you.

Best,

Sunny

Re: Group by a dimension.

sunny_talwar — Mon, 22 Jun 2015 17:16:49 GMT

Which warehouse name?

Re: Group by a dimension.

markgraham123 — Mon, 22 Jun 2015 17:17:46 GMT

Derz a Warehouse name OB2 mentione din the screenshot.

All the values correspond to that.

Re: Group by a dimension.

Anonymous — Mon, 22 Jun 2015 17:22:25 GMT

Mark

Your expression labelled SKUs is :

If((Sum([Qty On Hand])/([360-day Velocity]/52))>=0 and (Sum([Qty On Hand])/([360-day Velocity]/52))<=8, Count(Distinct(SKU#)))

Within it you have field [360-day Velocity] with no aggregation function so it will default to using the Only() function and unless all the values are the same for every row in each discrete warehouse it will return null() and you will get the results you are experiencing.

How about doing the condition in the load script and creating at the row level a new flag field with something like

If((Sum([Qty On Hand])/([360-day Velocity]/52))>=0 and (Sum([Qty On Hand])/([360-day Velocity]/52))<=8, 1,0) as [360LE8Flag] ,

This flag can then be used in Set analysis in your Chart object.

Backing off such calculations into the load script is always good for efficiency, as they are just calculated once during the load as opposed to dynamically in the GUI whilst the End Users are twiddling there thumbs and getting frustrated.

When you get the first Chart Expression working then do the other expressions similarly.

Re: Group by a dimension.

sunny_talwar — Mon, 22 Jun 2015 17:24:26 GMT

You are trying to get this?

Warehouse	SKUs	Units	Pallets
OBS	118187	13150406	2799319

Re: Group by a dimension.

markgraham123 — Mon, 22 Jun 2015 17:27:21 GMT

Hi,

I cannot include it in the script part,

Coz derz already preceeding load, resident laod and joins in the script which makes few fields unavailable.

Any solution to do it in Front end??

Please lemme knw.

Re: Group by a dimension.

markgraham123 — Mon, 22 Jun 2015 17:41:40 GMT

Yes.

How did u do it?

Re: Group by a dimension.

sunny_talwar — Mon, 22 Jun 2015 17:48:27 GMT

That was a question, I have not done anything yet

Re: Group by a dimension.

sunny_talwar — Mon, 22 Jun 2015 18:07:23 GMT

Try these expressions:

1) If(Dimensionality() = 0, Sum(Aggr(If((Sum([Qty On Hand])/([360-day Velocity]/52))>=0 and (Sum([Qty On Hand])/([360-day Velocity]/52))<=8, Count(Distinct(SKU#))), SKU#, Warehouse)))

2) If(Dimensionality() = 0, Sum(Aggr(If((Sum([Qty On Hand])/([360-day Velocity]/52))>=0 and (Sum([Qty On Hand])/([360-day Velocity]/52))<=8,Sum([Units Packed])), SKU#, Warehouse)))

3) If(Dimensionality() = 0, Sum(Aggr(If((Sum([Qty On Hand])/([360-day Velocity]/52))>=0 and (Sum([Qty On Hand])/([360-day Velocity]/52))<=8, Aggr(Count(LBCD),SKU#)), SKU#, Warehouse)))

How do you determine which warehouse name will show up there? Is it always going to be OB2?

If yes than you can do this:

HTH

Best,

Sunny

Re: Group by a dimension.

markgraham123 — Mon, 22 Jun 2015 18:18:57 GMT

Hi Sunny,

Its working.

But can i please knw how to enable all warehouse statistics at a time .

Since it is showing only OB2 / one warehouse at a time.