Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, everyone! I'm trying to get the highest/max net sales for a day by location and then the sales/business date it occurred on. The maximum Net Sales is working correctly, but I can't get it to pull in the SalesDate. Here are the two expressions I'm using.
//TO GET THE MAXIMUM OR HIGHEST SALES AMOUNT
max({$<SalesDate-={}>} NetSales)
//TO GET THE SALES DATE THE MAXIMUM SALES AMOUNT OCCURED ON
max({$<NetSales={$(=max({$<SalesDate-={}>} NetSales))}>} SalesDate)
I've tried several different set analysis formulas to get the date and I can't seem to figure it out. Does it have something to do with the date field? The date format being used in the load scipts/tables is MM/DD/YYYY
Also, as you can see, I am using StoreName as my only dimension in this table.
I would solve it like this:
=FirstSortedValue(DISTINCT SalesDate, -aggr(sum(NetSales), SalesDate, StoreName))
-Rob
I would solve it like this:
=FirstSortedValue(DISTINCT SalesDate, -aggr(sum(NetSales), SalesDate, StoreName))
-Rob
Thanks, Rob! That worked great! What if I also wanted to add another
expression the straight table to get the the maximum by day (i.e.
WeekDayName= 'Sat') Can I add another field to the sort or do I have to
add it as a set analysis within that formula you wrote?
=FirstSortedValue(DISTINCT NetSales, -aggr(sum(NetSales), SalesDate,
StoreName))
Thanks!
Ross
On Thu, May 31, 2012 at 4:28 PM, Rob Wunderlich <
Do you mean you want to display the Weekday name of the date that had the highest sales? Assuming the WeekDayname field already exists:
=FirstSortedValue(DISTINCT WeekDayname , -aggr(sum(NetSales), SalesDate, StoreName))
Or is it something else you are looking for?
-Rob