# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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.

Tags (4)
1 Solution

Accepted Solutions
MVP

## 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
MVP

## 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

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 <

MVP

## 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