sum only records of today's month

[#1]

Hi !

i am trying to create an expression that will sum a field but only when the record's date is today's month.

this is what i wrote and it's incorrect:

=sum({<MonthName(date) = {"monthName(Today())"} >} clicks)

for example:

date	clicks
10/1/2013	10
10/2/2013	3
10/3/2013	7
10/4/2013	8
10/5/2013	3
9/1/2013	2
9/2/2013	8
9/3/2013	3
9/4/2013	9
9/5/2013	2

i want the expression to sum only the records from October.

[israrkhan]

hi

first create a month field in load script like,

load

Date,

Month(Date) as Month,

Clicks

from abc

then in the expression write.

=sum({<Month = {$(=Month(Today()))}>}Clicks).

Hope it helps.

[#3]

no it doesnt work.

i attached the qvw

[#4 · Accepted solution]

i got it:

Sum({$<month1={$(=month(today()))}>}clicks)

[israrkhan]

See the attached.

let me know, how it is?

Khan

[#6]

Please try the below

Data:

Load date, clicks, monthname(date) as Months from abc;

Add a variable in settings/variable overview

vMonth

Set vMonth definition as

=monthname(today())

Use the below code in your calculation

=sum({<Months={"$(=vMonth)"}>} clicks)

Hope this will work for you

[#7]

another question along the lines of the above ...and if I have to take the value that returns me a field in a table instead of the value of a variable:

= sum ({<Months={"$(=vMonth)"}>} clicks)

For example add the clicks of a prior definition in a table:

Title - MonthsDef

D1 - 1,10,11,12 - Like "1 *"

D2 - 2,3,4,5,6,7,8,9 - Rest

With the following variable work,

September vMonth = (MonthDef like '1* ');

but if I had to use the field definition MonthsDef?????

[#8]

If I understood you correctly, I will use the below expressions

For Oct, Nov, Dec, Jan

=sum({<Months={"Oct*","Nov*","Dec*","Jan*"}>} clicks)

For the rest

=sum({<Months={"Feb*","Mar*","Apr*","May*","Jun*","Jul*","Aug*","Sep*"}>} clicks)

The reason for using a * is because I have used the Monthname in my previous example. The Monthname function returns the month and year of the date.

Is this what you were looking for?

[#9]

No, I have not explained well to follow the example and my English is not very good, I mean:

I got it with variables, for example:

Set vGP= (Cuenta like '640*' or like '641*')

and then a joint operation of this type if it works:

=sum({<Cuenta = { $(#vGP) }>} Importe)

But do not get this:

Titulo	Filtro
……	……
GP	640,641
…..	…….

For a table of such movements:

Cuenta	Valor
640000	1
640001	2
641000	3
641001	4

[#10]

In this case I will do the following

Add a variable vGP in Variable Overview

Set the definition of vGP as  =' "640*","641*" '

The single quote and double quotes are very important in this case

Then the I use the below expression

sum({<Cuenta={$(vGP)}>} Importe)

Is this what you are looking for?