How to get day and month from date

[#1]

Hi all,

i have a table like this

Day	values
2012/01/01	2
2012/06/01	3
2013/01/01	7
2013/06/01	4

if a select 2012/01/01 from a combo box i need that in a graph wiil be only the values where day and month are equals(in this case 2 and 7)

tyvm

Simone

[swuehl]

Maybe create two additional fields in your table containing day (or the master calendar):

LOAD *,

          Month(Day) as Month,

          Day(Day) as DayOfMonth;

LOAD

     date#(Day,'YYYY/MM/DD') as Day,

     values

FROM ...;

Then in your chart, use something like

=sum({<Month = p(), DayOfMonth = p(), Day= >} values)

[salto]

Hi,

You need to extract Month and Day Number from "Day" field:

1 .- num(Month(DayField)) -> 11 for 26/11/2013

2.-  num(Day(DayField)) -> 26 for  26/11/2013

Then build your graph expressions with set analysis taking into account only Month and Day, and disregarding all other selections.

Hth.

[VishalWaghole]

Hi simone,

Try this,

LOAD Month(Day) as CalenderMonth,

          Day(Day) as CalenderDay

FROM TableName;

Thanks and Regards,

Vishal Waghole

[#5]

hi salto,

i extracted the value of day and month from field "Day" but i didn't understand how to pass it in expression

tyvm

[MayilVahanan]

Hi

Try like this

PFA

[jagan]

Hi,

Just select Day and Month in list box and don't select year, qlikview automatically filters the remaining rows.  You don't need to pass this in expression.  You just use this script and expression

Data:

LOAD *,

          Month(Day) as Month,

          Day(Day) as DayOfMonth;

LOAD

     date#(Day,'YYYY/MM/DD') as Day,

     values

FROM ...;

=Sum(Measure)

If you want to explicitly mention in expression without selection then use like this

Sum({<Month={1}, Day ={1}>} Measure)

or

Sum({<Month={'Jan'}, Day ={1}>} Measure)

Hope this helps you.

Regards,

Jagan.

[#8]

i  this expression in expression tab

=Sum({<Day(fieldDay)={31},Month(fieldDay)={1}>} Values)

but it doesn't work, there is a error

I'm very close to solution!!!!

tyvm

Simone

[swuehl]

You can only use field names on the left side of a field modifier, not functions.

Have you tried what I suggested above?

[jagan · Accepted solution]

Hi Simone,

You should use only Dimensions in the Set Analysis expression, so you use the following script and reload

Data:

LOAD *,

          Month(Day) as Month,

          Day(Day) as Day;

LOAD

     date#(Day,'YYYY/MM/DD') as Day,

     values

FROM DataSource

Now you will two new dimensions Month and Day.

Sum({<Month={1}, Day ={31}>} Measure)

or

Sum({<Month={'Jan'}, Day ={31}>} Measure)

Hope this helps you.

Regards,

Jagan.