Wow, this is just the best explanation of a mystery that bothered me for many years!. I like how you broke it down into different steps and showed incrementally how the formulas worked!
I've been a Sr. Salesforce Admin going on 10 years now and have never, EVER, seen PREVGROUPVAL explained in such an easily digestible way! Subscribed 😃
This is so great, thank you. I have done these a few times and always have to get guided through them because I forget. What about when you want to want to just do a record count comparison rather than amount? I happen to have Power of 1 fields (if you have heard of those) and use those, and that can work, is it Rowcount or something?
I know what you mean: part of the reason I made this video was because I am tired of forgetting 😅I think you would have to select the "unique" count summary value (in the formula builder) from whatever field you have grouped, to get what you're asking. So for ex. instead of AMOUNT:SUM, I could've selected AMOUNT:UNIQUE (though that would've probably ended up being incorrect in my final report). But hopefully that example makes sense
Thanks, Moira! Short answer is that you'll want to 1) plug the specific row grouping you're concerned about into the formula AND 2) experiment with that display tab in the formula, to get the numbers to show the way you want. Different formula, but you might find my PARENTGROUPVAL video helpful (since I use a matrix report example there). Good luck! th-cam.com/video/JYhlmkdjYyY/w-d-xo.html
@@employedforgood thank you for the additional information. I have one other question - I have been struggling to get % Change to populate next to Total Amount on the top. Any ideas?
@@moirakennedy3524 Once you create the formula, it should appear automatically at the top of the report (if that's what you meant?) But otherwise, the display tab within the formula builder is how you'd set "where" the formula appears. You can also change order of your formula & other fields in the "Outline" tab of the report builder. Hard to tell without seeing it, but hopefully one of these is the answer
Hi, I Looooove your TH-cam content, you're helping me a lot with the PrevGroupVal and the ParentGroupVal. I have a question though, is there a way that I can sum all the Previous Values until that row? For example, Sales Month 1: $5, Sales M2: $4, Sales M3: $6 and Sales M4: $7. I would like to create a comparative in the Summary Level of the actual sale versus the SUM of the previous months, so example the new Column will have: Month1 Sum: 0 (because is the first one and Preval doesn't have that value), M2 Sum: $5 (The amount of M1), M3 Sum: $9 (The sum of sales of previous months M1+M2) and M4 Sum: $15 (SUM of M1+M2+M3). Is there a way? Thank you so much!
Thanks so much Abraham! I'm glad you find these helpful. I don't have the answer off the top of my head, but plan to do another video on these formulas very soon. (I've gotten similar questions, and when it comes to these formulas specifically, I really have to sit with them for a while before they make sense). I'll keep you posted!
Is there a way to display same month from different years next to eachother?? Nov 2021 & Nov 2022 next to each other? When I group by calndar month I cant seem to bring them next to eachother.
Here's what I would do. I would create a row level formula that captures the month (in number format) of the date field you're concerned about. Group your report rows by that field, and sort in ascending order. Then, add your date field as a column grouping, grouping that date by calendar year. I believe that should show Nov 2021 & Nov 2022 side by side. If you need help with that row level formula, you can use the month function: MONTH(your_date)
@@employedforgood Thank you! I found another way by creating a rowlevel formula CASE(MONTH(sfal__Appointment__c.Start_Date__c), 1, "A", 2, "B", 3, "C", 4, "D", 5, "E", 6, "F", 7, "G", 8, "H", 9, "I", 10, "J", 11, "K", 12, "L", "None") and grouped it by row and below that I have grouped the "start date" and grouped it by Calendar year and again below that created another custom field start date and grouped it by calendar month. The reason why I chose alphabets over the months name in my formula is when I sort the column the months get sorted alphabetically and the months get scattered.
@@Umashankaruttathur Glad you came to a solution that works! I almost suggested a CASE function like yours that returns the month abbreviations ("Jan", "Feb", etc..) but remembered exactly what you said: the sorting would always be off. That said, using the MONTH function to grab the month value would just return a number (1 for Jan, 2 for Feb, 3 for March, etc....). So if you found that it was hard to follow along with the letters you're returning, you can always use just the MONTH and then sort your grouping from smallest to highest.
Very well explained. Keep up the fantastic work!
Way easier to understand than the Salesforce documentation, that's for darn sure!
Ha thanks, much appreciated!
Thank you thank you thank you thank you!!!! :) I 've spent hours yesterday trying to fix a report until I came across your video. Cheers from Italy!
Awesome thanks for sharing, glad to hear it!!
Wow, this is just the best explanation of a mystery that bothered me for many years!. I like how you broke it down into different steps and showed incrementally how the formulas worked!
Awesome, thanks so much!! Glad to help unravel the mystery ^_^
Very good demonstration. Clear and clean !!!
Thanks for this!
I've been a Sr. Salesforce Admin going on 10 years now and have never, EVER, seen PREVGROUPVAL explained in such an easily digestible way!
Subscribed 😃
Thanks so much! The goal was definitely to make it make sense (once I finally figured it out 😂)
Wow. you are really awesome. You explained very well. Kudos to your work.
Thanks @pampariabhiram01, much appreciated!
This was awesome, thanks so much!
This is so great, thank you. I have done these a few times and always have to get guided through them because I forget. What about when you want to want to just do a record count comparison rather than amount? I happen to have Power of 1 fields (if you have heard of those) and use those, and that can work, is it Rowcount or something?
I know what you mean: part of the reason I made this video was because I am tired of forgetting 😅I think you would have to select the "unique" count summary value (in the formula builder) from whatever field you have grouped, to get what you're asking.
So for ex. instead of AMOUNT:SUM, I could've selected AMOUNT:UNIQUE (though that would've probably ended up being incorrect in my final report). But hopefully that example makes sense
does it work to compare dates?
Hi - great video! I'm curious how to do this but with two groups instead of just the one? Thanks!
Thanks, Moira! Short answer is that you'll want to 1) plug the specific row grouping you're concerned about into the formula AND 2) experiment with that display tab in the formula, to get the numbers to show the way you want.
Different formula, but you might find my PARENTGROUPVAL video helpful (since I use a matrix report example there). Good luck! th-cam.com/video/JYhlmkdjYyY/w-d-xo.html
@@employedforgood thank you for the additional information. I have one other question - I have been struggling to get % Change to populate next to Total Amount on the top. Any ideas?
@@moirakennedy3524 Once you create the formula, it should appear automatically at the top of the report (if that's what you meant?) But otherwise, the display tab within the formula builder is how you'd set "where" the formula appears. You can also change order of your formula & other fields in the "Outline" tab of the report builder.
Hard to tell without seeing it, but hopefully one of these is the answer
Hi, I Looooove your TH-cam content, you're helping me a lot with the PrevGroupVal and the ParentGroupVal. I have a question though, is there a way that I can sum all the Previous Values until that row? For example, Sales Month 1: $5, Sales M2: $4, Sales M3: $6 and Sales M4: $7. I would like to create a comparative in the Summary Level of the actual sale versus the SUM of the previous months, so example the new Column will have: Month1 Sum: 0 (because is the first one and Preval doesn't have that value), M2 Sum: $5 (The amount of M1), M3 Sum: $9 (The sum of sales of previous months M1+M2) and M4 Sum: $15 (SUM of M1+M2+M3). Is there a way? Thank you so much!
Thanks so much Abraham! I'm glad you find these helpful. I don't have the answer off the top of my head, but plan to do another video on these formulas very soon. (I've gotten similar questions, and when it comes to these formulas specifically, I really have to sit with them for a while before they make sense). I'll keep you posted!
Is there a way to display same month from different years next to eachother?? Nov 2021 & Nov 2022 next to each other? When I group by calndar month I cant seem to bring them next to eachother.
Here's what I would do. I would create a row level formula that captures the month (in number format) of the date field you're concerned about. Group your report rows by that field, and sort in ascending order.
Then, add your date field as a column grouping, grouping that date by calendar year. I believe that should show Nov 2021 & Nov 2022 side by side.
If you need help with that row level formula, you can use the month function: MONTH(your_date)
@@employedforgood Thank you! I found another way by creating a rowlevel formula CASE(MONTH(sfal__Appointment__c.Start_Date__c),
1, "A",
2, "B",
3, "C",
4, "D",
5, "E",
6, "F",
7, "G",
8, "H",
9, "I",
10, "J",
11, "K",
12, "L",
"None") and grouped it by row and below that I have grouped the "start date" and grouped it by Calendar year and again below that created another custom field start date and grouped it by calendar month. The reason why I chose alphabets over the months name in my formula is when I sort the column the months get sorted alphabetically and the months get scattered.
@@Umashankaruttathur Glad you came to a solution that works! I almost suggested a CASE function like yours that returns the month abbreviations ("Jan", "Feb", etc..) but remembered exactly what you said: the sorting would always be off.
That said, using the MONTH function to grab the month value would just return a number (1 for Jan, 2 for Feb, 3 for March, etc....). So if you found that it was hard to follow along with the letters you're returning, you can always use just the MONTH and then sort your grouping from smallest to highest.