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

[vishsaggi]

Can you tell us why you need ->  ReportDate = MIN(total <UserId, Date> Date) in your if when you already looking for a specific Report date = '31/08/2016' ?

May be try this?

= IF( Sum( Aggr(Firstsortedvalue(Amount, UserID), UserID)) >= 10 AND ReportDate = '31/08/2016', SUM(Amount))

[#3]

Hi,

I tried that but it didn't work.

So regarding why I need "ReportDate = MIN(total <UserId, Date> Date)". This is because I would like to find the minimum Date (and the relevant Sales Amount) for each UserID where they spent at least Amount >= 10 and it is between the date range 18/08/2016 and 04/09/2016. However, we only need to include the min. date for the UserID (where Amount >=10) yesterday's data (we have chosen 31/08/2016 as yesterday).

This is because we plan to create a daily report in the future  (with new date ranges etc.) and where we need to deliver a daily report which delivers the date and the relevant Sales Amount for that day.

[sunny_talwar]

There are lot of conditions here, it might be easier to look at a sample so that we can play around with it. Would you be able to share one? Also, make sure to clearly specify what you are looking to get

[#5]

Hi Sunny,

Thanks for helping out.

I've added an edit to my original post which includes the sample data and sample QVW.

As you will notice from the Excel sample data everything in yellow highlight should not be included (all UserIds from and including 100043) in the resultant output.

Thanks!

[sunny_talwar]

Thanks for providing a sample, but unfortunately, I am still not sure I understand the output you are looking to get? Do you want to see all dates between 18/08/2016 and 31/08/2016 in your chart or do you just want to see 31/08/2016? What would the expression need to show the Sum(Sales Amount) associated with the minimum date for an UserId? Why is 1000443 excluded??? Not sure I understand why we need to exclude it?

[#7]

Hi Sunny,

Answers in line:

"Thanks for providing a sample, but unfortunately, I am still not sure I understand the output you are looking to get?"

I am looking to get a daily report, in this example it would be run for the 31/08/2016. This means that only matches to the minimum date conditions I've included above that equal the 31/08/2016 are valid.

"Do you want to see all dates between 18/08/2016 and 31/08/2016 in your chart or do you just want to see 31/08/2016?"

My apologies, the data had a few errors with all UserIDs after 1000443 were copied across all UserIDs' I've updated the file, please see above.

Yes, this is part of the condition range but is not part of the output i.e. we need to identify the minimum date but we are to produce a daily report where that minimum date is equal to that day's report i.e. in this example we use 31/08/2016. In essence this would run for every day of the date range i.e. 18th & 19th & 20th etc. and so on.

The expression should show the filtered results for the 31/08/2016. This is from the the set of minimum dates that range from 18/08/2016 to 31/08/2016 where the Sales Amount >= 10. In other words every UserID will have a minimum date but only some of these (1000001 - 1000442) will be included for the 31/08/2016 day report output. All other UserIDs will be outputed on different daily report days e.g.

• 1000443 should return minimum date of 18/08/2016 (Sales Amount = 1437) - so this would be in a daily report on the 18/08/2016
• 1000444 should return minimum date of 24/08/2016 (Sales Amount = 1887) - so this would be in a daily report on the 24/08/2016
• 1000445 should return minimum date of 18/08/2016 (Sales Amount = 10) - so this would be in a daily report on the 18/08/2016
• 1000446 should not return minimum date as Sales Amount criteria not met as all values are below 10 (Sales Amount > 10)

"Why is 1000443 excluded??? Not sure I understand why we need to exclude it?"

Hopefully the above explanation makes sense, let me know if you need anything else.

[oknotsen]

Please stop editing your initial post as it will keep putting it back into Moderation Queue. Moderators are not that active during these days, so it might result in your topic not being visible for half a day or more.

Instead, just post a reply to your topic like I just did.

Qlik Community Team member

[#9]

Thank you Onno, duly noted.

Happy Holidays!

[vishsaggi]

Can you try this and let me know what else needs to be filtered ?