Skip to main content

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.

@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.

 

@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

 

@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.
 

 

@ray20 Sort Order allows a calculation like so

 

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

@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?

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 :)

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.

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