Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a question on Year to date formula.
We use the formula below, to calculate the Year to date.
=Sum (all if(Actual_Data.Month1 >= date(yearstart(Variable1),'DD/MM/YYYY') and Actual_Data.Month1<=Variable1,Actual_Data.Act_Data/1000000 )) where variable1==max(Actual_Data.Month1).
Consider, I have two selection criteria, 'Business' and 'Group'. When we select either the group or business, the result is not affected. So, the formula is changed to
=Sum (all <Business,Group> if(Actual_Data.Month1 >= date(yearstart(Variable1),'DD/MM/YYYY') and Actual_Data.Month1<=Variable1,Actual_Data.Act_Data/1000000 )). But, this also didn't help.
Can someone help me with the formula. I need to display the values in the straight table.
i got the formula.
Hi Santhosh
From your request I think you were almost there ... Total rather than all and you don't need the subtotals (<Business,Group>)
I have quickly knocked up a quick example (see attached)
I hope this helps
Hi Keeble,
I appreciate your help.
The file you have designed is not working for YTD as expected. My requirement was, if I select 2nd month, the result should be a sum of 1st and 2nd month and so on, but your result was just the sum of 2nd month. Is there any other way to do that?
Hi,
I`m following this thread for past two days but no replies.
Plz help on this
- Sridhar
Hi Santhosh
Sorry for the delay but I think I understand what you want to do now.
I would not do the formula in the table as it is not the most efficient as you will calculate every transactional record to calculate YTD.
I would do the aggregation in the script. If you have a look at the revised attachment and it is the YTD1 field which I then use in the tables.
Hope this makes some sense ?
Cheers
Hi Santhosh,
Check the attached application and let me know if it works.
I have tested this app, Working perfectly as expected.
Please check and let me know.
- Sridhar