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.

[anbu1984]

Check this

[#3]

Hi Anbu,

Thanks for your reply.

It is working in some cases but all the cases. I was trying to do my self but unable to. Please help me.

Below 2 cases not working.

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

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

Can anybody help on this please??

Thanks,

Lakshmi.

[jagan]

Hi,

Check this

=If(GetSelectedCount(Yr) = 0 AND GetSelectedCount(Mth) =0 AND GetSelectedCount(Dt) > 0,

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

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

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

If(GetFieldSelections(Yr)=2014 And IsNull(GetFieldSelections(Mth)),

  Sum({<Mth={"<=$(=Num(Month(Now())))"}>}Sales),

Sum(Sales)

)))

Regards,

Jagan.

[anbu1984]

Is 3 similar to 1? Or For 3 you need total in Current year from Jan to Current month?

[anbu1984]

Check this

[#7]

Hi Anbu,

Thanks for reply, I need total of that month if select any month. All conditions I need is total for that month only.

I have added full set of data here. Please help, I am trying to learn.

My apologies for the wrong logic.

All the time I need total of the month.

1. Default total of the Month - if there is no selection of Year & Month (Default to Current year, Current month total)
2. If Year only selected and that is LT current year, show total of the DEC month for that Selected Year.
3. If Year only selected and that is Current year default total to Current month of current year
4. If Year, Month selected - total of that month for the selected year.
5. If Day only selected, show the current month total

Thanks,

Lakshmi.

[#8]

Hi Jagan,

All time the expression showing -1.

[#9]

Hi All,

Can anybody help on expression?

All the time I need total of the month.

1. Default total of the Month - if there is no selection of Year & Month (Default to Current year, Current month total)
2. If Year only selected and that is LT current year, show total of the DEC month for that Selected Year.
3. If Year only selected and that is Current year default total to Current month of current year
4. If Year, Month selected - total of that month for the selected year.
5. If Day only selected, show the current month total

Thanks,

Lakshmi.

[anbu1984]

Check this

Changed date format in script to D/M/YYYY