Getting a Count of Items whose Sales are above $25K

[#1]

Hello Q-Community,

Very new user so please be patient.

I'm trying to get a count of the products/ItemNumber that have generated over $25,000 in revenue/SalesAmount over a Trailing Twelve Month (TTM) time period (which keeps complicating things for me).

I've tried the following and get a dash error.

=count (distinct IF (Sum({<CalendarWrittenDate={'>=$(=AddMonths((MonthStart(Today())),-12))<=$(=MonthEnd(AddMonths((Monthend(Today())),-1)))'}>} SalesAmount >25,000,ItemNumber))

Any suggestions would be appreciated.

[PrashantSangle]

Hi ,

Try this,

=count (IF (Sum({<CalendarWrittenDate={'>=$(=AddMonths((MonthStart(Today())),-12))<=$(=MonthEnd(AddMonths((Monthend(Today())),-1)))'}>} SalesAmount) >25,000,distinct ItemNumber)

Regards,

[#3]

Hi Max,

First, Thank You for taking the time,

Nope, still getting a dash, the first thing I noticed is that the paren before the first IF statement is red, tried putting another paren at the end but same error.

The other thing is that distinct isn't being recognized and neither is the ItemNumber field.

Sure, it's just a syntax thing, but really struggling.

[its_anandrjs]

Try with the below script

=count(distinct IF( Sum({<CalendarWrittenDate = {'>=$(=AddMonths((MonthStart(Today())),-12))

                                                  <=$(=MonthEnd(AddMonths((Monthend(Today())),-1)))'}>} SalesAmount ) > 25000,  ItemNumber))

[PrashantSangle]

Hi,

Try this,

=IF(Sum({<CalendarWrittenDate={">=$(=AddMonths((MonthStart(Today())),-12))<=$(=MonthEnd(AddMonths((Monthend(Today())),-1)))"}>} SalesAmount)>25000,count(Distinct ItemNumber))

Regards,

[#6]

Hi Anand, thanks for joining in. But nope, still getting dash.

[#7]

Max,

Giving me some good numbers, but giving me a total count of items, doesn't seem to be recognizing only the Sales above $25K.

[#8]

This actually gave me the same count of my product/ItemNumber, but when I replace 0 with 25,000, I get numbers that in some cases are higher than my product count.

=count({<CalendarWrittenDate={'>=$(=AddMonths((MonthStart(Today())),-12))<=$(=MonthEnd(AddMonths((Monthend(Today())),-1)))'}>}DISTINCT IF (SalesAmount >0,ItemNumber) )

[PrashantSangle]

Hi,

I know it was pretty bad logic that i have given to you.

Sorry for that.

But you can try this,

Create Variable

Store value in variable

Then use that variable in your set analysis.

Like this,

sumSales=Sum({<CalendarWrittenDate={">=$(=AddMonths((MonthStart(Today())),-12))<=$(=MonthEnd(AddMonths((Monthend(Today())),-1)))"}>} SalesAmount)

Then use this variable inside your count()

Like this,

Count({<CalendarWrittenDate={">=$(=AddMonths((MonthStart(Today())),-12))<=$(=MonthEnd(AddMonths((Monthend(Today())),-1)))"},$(sumSales)={">25000"}>}ItemNumber)

Regards,

[#10]

Hey Max,

Not yet. First it I'm getting some high counts returned, like it's doing 4 years worth instead of the one TTM. The other thing I noticed is the error (squiggly red line) under neath the curly bracket right before the ">25000. And the ItemNumber isn't highlighted like a field.

I called my Variable TTMSales 14 and this is what I inputted.

=Count({<CalendarWrittenDate={">=$(=AddMonths((MonthStart(Today())),-12))<=$(=MonthEnd(AddMonths((Monthend(Today())),-1)))"},$(TTMSales14)={">25000"}>}ItemNumber)