Min. Sum(Sales) for UserID where Sum(Sales) >= 10 for Date between 18/08/2016 and 04/09/2016 - Daily report

[#1]

Hi,

I am struggling with an Expression, if anyone could please help it would be greatly appreciated!. I need to add the following criteria:

• Minimum Sum( Sales Amount) for all 'UserIDs'
• Where Sum( Sales Amount ) > = 10  (In a given day there could be multiple sales so we need the sum for that day)
• 'ReportDate' between '18/08/2016' and '04/09/2016''
• ReportDate' must = '31/08/2016' as it is a daily report and we are checking the minimum Sum(Sales) for that day only

I have tried using:

Calculated Dimensions:

• ReportDate: if(Date>= '18/08/2016' and Date<='04/09/2016', Date)

Expression:

= IF( ReportDate = MIN(total <UserId, Date> Date) AND Sum( Sales Amount ) >= 10 AND ReportDate = '31/08/2016', SUM(Amount))

The above clearly doesn't work and I am getting some really strange inclusions. Any help whatsoever will be highly appreciated!

Edit:

28 Dec. 2016:

Updated both files to include the correct UserIds as the previous file had an issue with a few of the trailing UserIds.

27 Dec. 2016:

I've added sample data below and a QVW. As you will notice from the Excel sample data everything in yellow highlight should not be inoth cluded (all UserIds from and including 100043) in the resultant output.

Thanks,

S W

[#2]

Hi Vishwarath,

This has been hugely helpful, however, there is something which i/ have not accounted for in the larger data set I am working on.

It seems there are multiple sales in a given day.

With your solution it delivers the first value for that day:

=Firstsortedvalue({< [Sales Amount]= {">=10"}>} [Sales Amount], Aggr(Min(Date), SbUserId))

What I need is the Total sales for the day rather than the first amount, so as an example if i changed UserID: 1000442 to:

Date	UserId	Sales Amount
31/08/2016	1000442	4
31/08/2016	1000442	6

This means that this UserId would be excluded. Is there any way to Sum the [Sales Amount] first before doing the >=10 filter and also return the sum?

Also, I need to return the date so if it could be modified to do that too, that would be great!

Thanks again for all your help!

[vishsaggi]

Can you tell me why this UserID needs to be excluded? This userId has the salesamount as 10 in your excel sheet, which qualified our >= 10 criteria? Can you elaborate?

Or if your data is different to what you have on your current excel sheet attached let us know or send me a new set of data you actually working on.

Try this attached?

= Firstsortedvalue({<[Sales Amount] = {'>=10'}>} Aggr(Sum([Sales Amount]), Date, UserId), Aggr(Min(Date), UserId))

[sunny_talwar]

We don't have a sample to test this, but try this:

=Sum(Aggr(If(Date = Min(TOTAL <UserId> Aggr(If(Sum([Sales Amount]) >= 10, Date), UserId, Date)), [Sales Amount]), UserId, Date))

[#5]

Hi Vishwarath,

I've tested that and it doesn't work with the new above Expression.

I've created a link for the files below:

• QVW file
• Excel file‌

Thanks and highly appreciated!

[#6]

Hi Sunny,

Thanks for your input, it's highly appreciated! I've added the files above in response to Vishwarath.

I just tried your qvw and updated the data and it's clear that UserID: 1000442 is missing from the data.

Also, as a reminder, I need this for 31/08/2016 (I probably can do this myself but not sure what the solution would be so worth noting for now).

Thanks,

Sjaun

[sunny_talwar]

May be this?

=Sum(Aggr(If(Date = Min(TOTAL <UserId> Aggr(If(Sum([Sales Amount]) >= 10, Date), UserId, Date)), Sum([Sales Amount])), UserId, Date))

[#8]

Thanks Sunny.

I've checked on of the UserIds and the numbers are incorrect - it seems to have summed all the values for the UserId, please see below image.

Thanks!

[sunny_talwar · Accepted solution]

Is this for me or Vishwarath? I guess you responded to one of my threads... Did you try what I suggested?

=Sum(Aggr(If(Date = Min(TOTAL <UserId> Aggr(If(Sum([Sales Amount]) >= 10, Date), UserId, Date)), Sum([Sales Amount])), UserId, Date))

[#10]

Thank You, Thank You, Thank You Sunny!! That works like a charm. I made a few adjustments to your expression but not much. Works beautifully though!

You've saved my holidays!

Also, a big shout out to Vishwarath who also helped us get to this point!

Min Dep. Amount:

=IF(Sum(Aggr(If(Date = Min(TOTAL <UserId> Aggr(If(Sum([Sales Amount]) >= 10, Date), UserId, Date)), Sum([Sales Amount])), UserId, Date))>=10,

IF(

Min(TOTAL <UserId> Aggr(If(Sum([Sales Amount])>= 10, Date), UserId, Date), Sum([Sales Amount]))

= '31/08/2016',

Sum(Aggr(If(Date = Min(TOTAL <UserId> Aggr(If(Sum([Sales Amount]) >= 10, Date), UserId, Date)), Sum([Sales Amount])), UserId, Date)))

)

Min Dep. Date:

=IF(Sum(Aggr(If(Date = Min(TOTAL <UserId> Aggr(If(Sum([Sales Amount]) >= 10, Date), UserId, Date)), Sum([Sales Amount])), UserId, Date))>=10,

IF(

Min(TOTAL <UserId> Aggr(If(Sum([Sales Amount])>= 10, Date), UserId, Date), Sum([Sales Amount]))

= '31/08/2016', '31/08/2016')