Set analysis question

[mszuberl]

I have a sample data table, like the one attached.

Want to create a calculate dimension that would calculate to be the date corresponding with highest Sales, for each Site, Department.

I have the following, but it does not work as expected.

=aggr(concat({<Date={"=Max( total aggr( sum(Sales),Date))=aggr(sum(Sales),Date)"}>} DISTINCT Date), Date,Site,Department)

Please help.

[mszuberl]

sample QVF attached

[mszuberl]

Anyone?

[JustinDallas]

In the future, you may want to refrain from using RANDBETWEEN in your dummy data excel file.  It makes the data impossible to validate between users, and makes it more difficult for us to help you because we don't assume that the data will "shake" when we move around the sheet. So if we come to a solution, we can't verify it.

I would stick with tried and true Inline statements for dummy data, like so:

DummyData:
LOAD *, Date#(DateString, 'MM/DD/YYYY') AS 'Date'
;
LOAD * Inline 
[
'Site','Department','DateString','Sales'
'A','1','01/01/2020','1619'
'A','2','01/02/2020','938'
'A','1','01/03/2020','674'
'B','2','01/04/2020','823'
'B','1','01/05/2020','620'
'B','3','01/06/2020','583'
'A','1','01/07/2020','1107'
'B','2','01/08/2020','638'
'B','1','01/09/2020','890'
'B','3','01/10/2020','1170'
'C','4','01/11/2020','1583'
'C','1','01/12/2020','732'
'C','2','01/13/2020','1349'
'C','3','01/14/2020','303'
'C','1','01/15/2020','1787'
'C','1','01/16/2020','333'
]
;

[mszuberl]

JustinDallas,

My bad. Sorry for that. Forgot to replace formulas with values.

Marcin

[andrew_smith200]

I loaded in the inline script and the calculated dimension

=aggr(concat({<Date={"=Max( total aggr( sum(Sales),Date))=aggr(sum(Sales),Date)"}>} DISTINCT Date), Date,Site,Department)

created in a a bar chart - worked perfectly - suspect it would be better in a KPI extension though!!