Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data as shown below:
Year | Month | Sales |
---|---|---|
2000 | January | 10 |
2000 | February | 10 |
2000 | March | 10 |
2000 | April | 10 |
2000 | May | 10 |
2000 | June | 10 |
2000 | July | 10 |
2000 | August | 10 |
2000 | September | 10 |
2000 | October | 10 |
2000 | November | 10 |
2000 | December | 10 |
2001 | January | 20 |
2001 | February | 20 |
2001 | March | 20 |
2001 | April | 20 |
2001 | May | 20 |
2001 | June | 20 |
2001 | July | 20 |
2001 | August | 20 |
2001 | September | 20 |
2001 | October | 20 |
2001 | November | 20 |
2001 | December | 20 |
I need to do Sum of Sales between January of Selected year till Selected Month of that Year .for eg In above scenario if I select Year =2000(listbox) and Month=March(listbox) then expression should show sum= 30.
Hi
Do you have date field?
If so, use like this,
=Sum({<DateField = {'>=$(=MonthStart(Min({1}DateField))) <= $(=MonthEnd(DateField))'}>}Sales)
Hope it helps
Hi Mayil,
Thanks for response. Its giving me the result as 0.
As we are using Year and Month as List box in Dashboard ,the Date Field is present in the script but not in Dashboard So user can only select Year and Month not Date Field.
Hi Mayil,
Thanks for response. Its giving me the result as 0.
As we are using Year and Month as List box in Dashboard ,the Date Field is present in the script but not in Dashboard So user can only select Year and Month not Date Field.
Hi
Can you post a sample file?
Try using function InYearToDate(). Probably like this, or close to it (assuming Month has numeric value, and Date is sales date):
sum({1} if(InYearToDate(Date, monthend(makedate(Year,Month)), 0), Sales))
Regards,
Michael