14 Replies Latest reply: Sep 15, 2014 10:14 AM by anbu cheliyan

# Help needed on Expression

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

Regards,

Lakshmi.

• ###### Re: Help needed on Expression

Check this

• ###### Re: Help needed on Expression

Hi Anbu,

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.

• ###### Re: Help needed on Expression

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.

• ###### Re: Re: Help needed on Expression

Hi Jagan,

All time the expression showing -1.

• ###### Re: Help needed on Expression

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

• ###### Re: Re: Help needed on Expression

Check this

• ###### Re: Re: Help needed on Expression

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.

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.

• ###### Re: Re: Help needed on Expression

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.

• ###### Re: Re: Re: Help needed on Expression

Check this

Changed date format in script to D/M/YYYY

• ###### Re: Help needed on Expression

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.

• ###### Re: Help needed on Expression

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)))

• ###### Re: Re: Help needed on Expression

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.

• ###### Re: Help needed on Expression

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))))