16 Replies Latest reply: Jun 22, 2015 2:18 PM by Mark Graham

Group by a dimension.

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.

Can you reduce the size of the document?

• Re: Group by a dimension.

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.

• Re: Group by a dimension.

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.

Great and is this the part you are looking for?

and nothing below it, right?

• Re: Group by a dimension.

Yes.

Exactly.

• Re: Group by a dimension.

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.

With a warehouse name.

• Re: Group by a dimension.

Which warehouse name?

• Re: Group by a dimension.

Derz a Warehouse name OB2 mentione din the screenshot.

All the values correspond to that.

• Re: Group by a dimension.

You are trying to get this?

WarehouseSKUsUnitsPallets
OBS118187131504062799319
• Re: Group by a dimension.

Yes.

How did u do it?

• Re: Group by a dimension.

That was a question, I have not done anything yet

• Re: Group by a dimension.

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.

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.

• Re: Group by a dimension.

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.

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