Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

rluciano1012
New Contributor II

How can use set analysis to find maximum sales and the date it occurred on in a straight table?

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.

StraightTableResult.jpg

1 Solution

Accepted Solutions

Re: How can use set analysis to find maximum sales and the date it occurred on in a straight table?

I would solve it like this:

=FirstSortedValue(DISTINCT SalesDate, -aggr(sum(NetSales), SalesDate, StoreName))

-Rob

http://robwunderlich.com

3 Replies

Re: How can use set analysis to find maximum sales and the date it occurred on in a straight table?

I would solve it like this:

=FirstSortedValue(DISTINCT SalesDate, -aggr(sum(NetSales), SalesDate, StoreName))

-Rob

http://robwunderlich.com

rluciano1012
New Contributor II

Re: How can use set analysis to find maximum sales and the date it occurred on in a straight table?

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 <

How can use set analysis to find maximum sales and the date it occurred on in a straight table?

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

Community Browser