Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
Please help with a simple formula to Sum a range of Account numbers as follows:
1. Sum Amount for the Accounts 5722 to 5728 ( I want to state a range NOT to list them all)
2. Sum Amount for the range of Accounts 2300 to 2480
3. Then Subtract (2.) from (1.)
Below is just a sample , the Accounts are way too many than this hence the reason why i cant use a formula such as
WHERE Acc IN (5722, 5722.........)
Acc | Amount | |
5722 | 6000 | |
5723 | 850 | |
5724 | 6850 | |
5725 | 7700 | |
5726 | 14550 | |
5727 | 22250 | |
5728 | 36800 | |
3469 | 9625 | |
2300 | 14300 | |
2330 | 6400 | |
2360 | 7900 | |
2390 | 4500 | |
2420 | 3400 | |
2450 | 1100 | |
2480 | 2300 |
If account is numeric you could try
sum({<Account={'>=2300<=2480'}>}Amount) - sum({<Account={'>=5722<=5728'}>}Amount)
If account is numeric you could try
sum({<Account={'>=2300<=2480'}>}Amount) - sum({<Account={'>=5722<=5728'}>}Amount)
That syntax with double quotes as long as it is a search, single quotes work, but it's a known bug.
Thanks @Wallo Atkinson, this worked correctly on my sample data.
I will apply then apply the formula on the model and see how it goes.
Once again, thank you
Good luck. Hope it helps.
Hi atkinsow, i applied the formula on my model. It worked like charm!
Thank you very much for helping here!