function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
naresh johnnaresh john 

Very very complex soql query please help me

HI Every one,

 

   I have a custom object "Transactions".The fields are Id, Name, Amount, TransactionDate. How to calculate % change(Increase or decrease ) in amounts month to month this year. In this format I need to display.

 

Month         Amount received     % change(When compared to last month) 

 

 

Kindly help me its my urgent requirement.If any one worked on same requirement, please provide the code here, It will be a great help.

 

Thanks in advance,

Naresh

Best Answer chosen by Admin (Salesforce Developers) 
sh-at-youseesh-at-yousee

Yes, I figured it would be possible to group by month. I just didn't have the time to do an actual test ;)

 

True, there are several ways of getting your resutl shown. Personally, I'd go for the VisualForce suggestion but that depends on what it is needed for.

 

/Søren Nødskov Hansen

All Answers

Imran MohammedImran Mohammed

I feel this kind of SOQL cannot be constructed.
naresh johnnaresh john

Thanks for the reply.

 

  If its not possible, then how to solve this requirement.Any other ways to do it.Can you please give me any idea of approach, so that I may work on it and get the result.

 

Thanks,

Naresh
sh-at-youseesh-at-yousee

Hi,

 

Maybe I'm misunderstanding but wouldn't you simply do something like this:

 

  1. Query backend for all Transaction__c objects where MONTH(TransactionDate) = MONTH(TODAY()) // All transactions for this month
  2. Query backend for all Transaction__c objects where MONTH(TransactionDate) = MONTH(TODAY()) - 1// All transactions for last month
  3. Sum the amount for this months transactions
  4. Sum the amount for last months transactions
  5. Do math to calculate change in percent

NB: The above is pseudo code and not something I've actually tested so it may (will) include typos and/or syntax errors.

 

Another way (again untested - just brain storming here) could be to query backend for all Transaction__c objects and group them by MONTH(Transaction__c). I'm not sure if this is actually possible.

 

Hope it helps.

 

/Søren Nødskov Hansen
fgwarb_devfgwarb_dev

You can group by month...

 

 

SELECT count(Id) qty, CALENDAR_MONTH(TransactionDate) month, CALENDAR_YEAR(TransactionDate) year, SUM(Amount) total FROM Transaction__c GROUP BY CALENDAR_MONTH(TransactionDate)

 

Once you've got that you can calculate your Month over Month in whatever format is preferred

 

You could write:

 

  • A VF page & Apex class so it's accessible on demand by whoever wants it
  • A monthly run Apex batch class that calculates the values and stores in a new object, combined with scheduled reports
  • Export it to Excel
  • Write a webservice & Excel macro to run the report through excel

 
sh-at-youseesh-at-yousee

Yes, I figured it would be possible to group by month. I just didn't have the time to do an actual test ;)

 

True, there are several ways of getting your resutl shown. Personally, I'd go for the VisualForce suggestion but that depends on what it is needed for.

 

/Søren Nødskov Hansen

This was selected as the best answer
naresh johnnaresh john

Thank you so much for the above help. Let me check that.