topic Re: Set Analysis, Month, Today in Straight Table in QlikView

Set Analysis, Month, Today in Straight Table

cpiocpio — Wed, 19 Feb 2014 22:57:44 GMT

Firstly, I thought I had posted earlier, but I can not find it, if I did and If you do, find it then my apologises

So in my straight table I need t return the SUM of a field, where the Dates in a field called : calDueDate is within the Current Month.

I used the Set Analysis Wizard to go my function statement, but I get nothing back

=Sum({1<Month(calDueDate)={$(=Month(Today()))}>}i_lifts)

Mike

Re: Set Analysis, Month, Today in Straight Table

swuehl — Wed, 19 Feb 2014 23:09:37 GMT

Mike,

in your set modifier's field selection, there is only a pure field name allowed to the left of the equal sign (please compare to the syntax description in the Help file).

You can create an additional field in your data model for month or year month and then use this field in your set modifier, like

=Sum({1<calDueMonthName = {'$(=Monthname(Today()))'}>} i_lifts)

or if you need to select on your date:

=Sum({1< calDueDate ={">=$(=Monthstart(Today()))<=$(=Monthend(Today()))"}>} i_lifts)

Re: Set Analysis, Month, Today in Straight Table

Wed, 19 Feb 2014 23:14:28 GMT

try like this

Create one variable like

Vtoday=max(month(calDueDate)) or month(today())

then implement the variable in set ananlysis

=Sum({<calDueDate={$(Vtoday)}>}i_lifts)

hope this will helps you

Re: Set Analysis, Month, Today in Straight Table

Thu, 20 Feb 2014 05:16:36 GMT

Mike,

Use like this

Sum({$<calDueDate={">$(=Monthstart(today()))<$(=Monthend(today()))"}>}Sales).

see the set analysis wizard will helpful to you creating expressions in set analysis.

i hope it helps you.

Reagrds

Mahesh T

Re: Set Analysis, Month, Today in Straight Table

cpiocpio — Thu, 20 Feb 2014 06:22:03 GMT

I had found a similar post earlier, and it did not work, just tried yours and same results. returned 0.

I used

Sum({$<calDueDate={">$(=Monthstart(today()))<$(=Monthend(today()))"}>}i_lifts)

Re: Set Analysis, Month, Today in Straight Table

cpiocpio — Thu, 20 Feb 2014 07:04:29 GMT

Many thanks for this

I did as you suggested, added a new "field"/Variable to result in a returned value of Feb 2014.

Month(d_duedate) & ' ' & Year(d_duedate) as calDueMonthName,

Then using

({1<calDueMonthName = {'$(=Monthname(Today()))'}>} i_lifts)

and it worked.

My prime mistake was having a function to the left of the = sign in a set analysis.

Re: Set Analysis, Month, Today in Straight Table

cpiocpio — Thu, 20 Feb 2014 07:05:56 GMT

Hi to ALL

Many thanks for your suggestions

I did as swuehl suggested, added a new "field"/Variable to result in a returned value of Feb 2014.

Month(d_duedate) & ' ' & Year(d_duedate) as calDueMonthName,

Then using

({1<calDueMonthName = {'$(=Monthname(Today()))'}>} i_lifts)

and it worked.

My prime mistake was having a function to the left of the = sign in a set analysis.