Yeartodate formula is not working for multiple selection.

[#1]

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.

[#2]

i got the formula.

[#3]

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

[#4]

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?

[#5]

Hi,

I`m following this thread for past two days but no replies.

Plz help on this

- Sridhar

[#6]

Normal 0 false false false MicrosoftInternetExplorer4

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

[#7]

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