Help needed on Expression

[#1]

Hi All,

I have to get total based on the selected Month and Year.

My table as below

Sales    Date

987    1/1/2014

776    2/1/2013

654    1/1/2012

656    11/2/2011

987    12/2/2011

776    11/5/2011

654    11/2/2011

656    12/2/2014

987    11/3/2014

776    1/4/2014

654    11/5/2014

656    1/4/2014

656    11/5/2014

776    11/2/2014

654    12/2/2014

656    11/3/2014

987    1/4/2014

776    11/5/2014

654    1/4/2014

656    11/5/2014

987    1/1/2014

776    2/1/2014

654    1/1/2014

Conditions to get total sales as below:

1. Default total if there is no selection of Year & Month (Default to Current year, Current month)

2. If Year only selected and that is LT current year default total based on DEC & Selected Year.

3. If Year only selected and that is Current year default total to Current year, Current month

4. If Year, Month selected - total of that Year and Month.

5. If Day only selected, default total to Current year, Current month

Thanks in advance for help.

Regards,

Lakshmi.

[#2]

Hi Anbu,

It is working for the year 2014, but for the year 2011/2012 if i choose any moth it is showing 3100, ex. Feb = 2800 but it is showing as 3100.

Thanks for help.

Regards,

Lakshmi.

[anbu1984]

Use this expr

=If((IsNull(GetFieldSelections(Yr)) And IsNull(GetFieldSelections(Mth))) Or

(GetFieldSelections(Yr)=2014 And IsNull(GetFieldSelections(Mth))) Or

(IsNull(GetFieldSelections(Yr)) And IsNull(GetFieldSelections(Mth)) And GetSelectedCount(Dy)>0)

,Sum({<Dy=,Yr={$(=Year(Now()))},Mth={$(=Num(Month(Now())))}>}Sales),

If(GetFieldSelections(Yr) < Year(Now()) And IsNull(GetFieldSelections(Mth)),Sum({<Mth={12}>}Sales),

Sum(Sales)))

[#4]

Hi Anbu,

Yes, working properly except when I choose month only. I forgot to mention that If month only choose, i have to consider current year.

Thanks a million for the help.

Regards,

Lakshmi.

[anbu1984]

Try this expr

=If((GetSelectedCount(Yr)=0 And GetSelectedCount(Mth)=0) Or

(GetFieldSelections(Yr)=2014 And IsNull(GetFieldSelections(Mth))) Or

(GetSelectedCount(Yr)=0 And GetSelectedCount(Mth)=0 And GetSelectedCount(Dy)>0)

,Sum({<Dy=,Yr={$(=Year(Now()))},Mth={$(=Num(Month(Now())))}>}Sales),

If(GetFieldSelections(Yr) < Year(Now()) And GetSelectedCount(Mth)=0,Sum({<Mth={12}>}Sales),

If(GetSelectedCount(Yr)=0 And GetSelectedCount(Mth)>0,Sum({<Yr={$(=Year(Now()))}>}Sales),

Sum(Sales))))

[anbu1984]

Please mark as answered if your queries are resolved