Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to get day and month from date

Hi all,

i have a table like this

Dayvalues
2012/01/012
2012/06/013
2013/01/017
2013/06/014

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

1 Solution

Accepted Solutions
MVP
MVP

Re: How to get day and month from date

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.

11 Replies
MVP
MVP

Re: How to get day and month from date

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
Valued Contributor II

Re: How to get day and month from date

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
Valued Contributor II

Re: How to get day and month from date

Hi simone,

Try this,

LOAD Month(Day) as CalenderMonth,

          Day(Day) as CalenderDay

FROM TableName;

Thanks and Regards,

Vishal Waghole

Not applicable

Re: How to get day and month from date

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

Re: How to get day and month from date

Hi

Try like this

PFA

MVP
MVP

Re: How to get day and month from date

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.

Not applicable

Re: How to get day and month from date

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

MVP
MVP

Re: How to get day and month from date

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

Have you tried what I suggested above?

MVP
MVP

Re: How to get day and month from date

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.

Community Browser