topic Re: Help needed on Expression in QlikView

Help needed on Expression

Sun, 14 Sep 2014 11:34:55 GMT

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.

Re: Help needed on Expression

anbu1984 — Sun, 14 Sep 2014 12:59:18 GMT

Check this

Re: Help needed on Expression

Mon, 15 Sep 2014 08:47:26 GMT

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.

Re: Help needed on Expression

jagan — Mon, 15 Sep 2014 08:58:53 GMT

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: Help needed on Expression

anbu1984 — Mon, 15 Sep 2014 09:00:10 GMT

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

anbu1984 — Mon, 15 Sep 2014 09:10:53 GMT

Check this

Re: Re: Help needed on Expression

Mon, 15 Sep 2014 09:49:13 GMT

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.

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

Thanks,

Lakshmi.

Re: Re: Help needed on Expression

Mon, 15 Sep 2014 09:50:48 GMT

Hi Jagan,

All time the expression showing -1.

Re: Re: Help needed on Expression

Mon, 15 Sep 2014 10:04:22 GMT

Hi All,

Can anybody help on expression?

All the time I need total of the month.

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

Thanks,

Lakshmi.

Re: Re: Re: Help needed on Expression

anbu1984 — Mon, 15 Sep 2014 10:18:35 GMT

Check this

Changed date format in script to D/M/YYYY

Re: Help needed on Expression

Mon, 15 Sep 2014 10:35:53 GMT

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

anbu1984 — Mon, 15 Sep 2014 10:41:52 GMT

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

Mon, 15 Sep 2014 10:51:51 GMT

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

anbu1984 — Mon, 15 Sep 2014 10:58:53 GMT

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

Re: Help needed on Expression

anbu1984 — Mon, 15 Sep 2014 14:14:25 GMT

Please mark as answered if your queries are resolved