Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

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"

16 Replies
Anonymous
Not applicable

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?

WarehouseSKUsUnitsPallets
OBS118187131504062799319
markgraham123
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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?

Capture.PNG

If yes than you can do this:

Capture.PNG

HTH

Best,

Sunny

markgraham123
Specialist
Specialist
Author

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.