Group by a dimension.

[markgraham123]

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"

[Anonymous]

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.

[sunny_talwar]

You are trying to get this?

Warehouse	SKUs	Units	Pallets
OBS	118187	13150406	2799319

[markgraham123]

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.

[markgraham123]

Yes.

How did u do it?

[sunny_talwar]

That was a question, I have not done anything yet

[sunny_talwar]

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

[markgraham123]

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.