Solved

Find out the top 10 monthly sales qty increasing/decreasing items in just 1 report.

  • 17 August 2022
  • 8 replies
  • 185 views
R
Rank
Userlevel 6
Badge +5

Hello,
      I have a need to find out the top 10 items with the most sales qty drops comparing to last month.
It could be done by sum up the figures group by item this month---------Result 1
And sum up the figures group by item last month-------Result 2
Then, I can do caculate in excel by comparing result 1 and result 2.

       I am just wondering is there any way that I can get the final result directly in Acumatica with GI or Report?       Thank you in advance.

icon

Best answer by DConcannon 17 August 2022, 23:11

View original

8 replies

DConcannon
Rank
Userlevel 6
Badge +2

@ray20 You can get the results, including calculations, in a single Report or GI. The basic setup is contained in the images below. In the interest of speed I hard-coded the month-apart periods directly in  Relations.

 

R
Rank
Userlevel 6
Badge +5

@DConcannon  Super. Thank you very much. The self join is a really smart move.

I can get the result I need by manually changing the join Relation/condition.
 


Do you know is it possible to make it automatically selecting the PrePeriod.
Like today, I would like to compare  Period “202207”(which is report Period) with “202206”(which is prePeriod)

I can make the report period automatically by putting conditions:
 

 

FinPeriod is a defines like below

 

R
Rank
Userlevel 6
Badge +5

@DConcannon 
Hi, good news. I get what I want by below formula:

=IIf( Right( [ItemCustSalesHist.FinPeriodID], 2)='01',CStr( ( Left( [ItemCustSalesHist.FinPeriodID], 4)-1))+'12', [ItemCustSalesHist.FinPeriodID]-1)

 

 

It works.

By the way, we have only a tiny loose end to tie, if I would like to make it fully automatically.

Do you know a way to just sorting by the calculated field directly? Or we just filter out the top 10.
 

 

DConcannon
Rank
Userlevel 6
Badge +2

@ray20 Sort Order allows a calculation like so

 

Thank you for your knowledge share on FinPeriod and the links within!

R
Rank
Userlevel 6
Badge +5

@DConcannon  Hello, I tried. Although I can type the calculation into the sort tab screen, it was not working as eppected. It did not work at all.
Did you get this working? which version are you working at? 2022R1?  
I am working on 2021R2. Is it this is not supported in 2021R2?

DConcannon
Rank
Userlevel 6
Badge +2

I tested before I posted 2021R1. Adding a screen shot or attaching the GI file would help us be of more help. Hope you figured it out in the meantime :)

R
Rank
Userlevel 6
Badge +5

Hello @DConcannon 
     Thank you for your time. It is a little bit complicated.
1, I calculate based on ItemCustSalesHis table, because we have to exclude some internal customers.
2, The qty drops have to count based on item attribute.
We have UsrCatchwgt = 1 item, which having baseUnit = lb, while we sell in case whith dynamic weight, so we have to divide by avg weight/qty to get the case qty.

 

I can get the things going by manually clicking the column header to sort.

1 Attachment
DConcannon
Rank
Userlevel 6
Badge +2

That makes sense. Calculations work as in displayed in my earlier post.  I have not seen IIf statements work in the Sort Order tab. I will look at your GI and see if I can offer an alternative to using the IIf statement in the Sort Order tab.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved