topic Re: Working with the function Date# in QlikView

Working with the function Date#

Mon, 23 Nov 2015 13:16:03 GMT

Hello everyone,

I am working with dates on QlikView and i am trying to display data of the last 12 months from today in a bar chart.

My data base has a field called "Data do Resultado", where there is a rule in excel to convert the date to the following format "MMM/YYYY", as you can see below:

In my script i have created the Date like that:

SET DateFormat='MMM/YYYY';

Date([Data do Resultado], 'MMM/YYYY') as [Data do Resultado],
Year([Data do Resultado]) as Year,
Month([Data do Resultado]) as Month,

In my bar chart where i have to show only the last 12 months, i have create a calculated dimension with the below expression:

if([Data do Resultado]>=AddMonths(today(),-12),Date#([Month],'MMM/YYYY'))

This expression works fine, but it only displays the Months:

I need to display the Date as Month/Year, so i changed the calculated dimension as follow:

if([Data do Resultado]>=AddMonths(today(),-12),Date([Data do Resultado],'MMM/YYYY'))

But with this expression it doesn´t show the last 12 months, but all months of my data base, so i have a chart with large data:

Anyone could help me to solve that??

Regards.

Re: Working with the function Date#

sunny_talwar — Mon, 23 Nov 2015 13:20:27 GMT

Not sure, but try this may be:

=If([Data do Resultado] >= AddYears(Today(), -1) and [Data do Resultado] <= Today(), [Data do Resultado])

Re: Working with the function Date#

Mon, 23 Nov 2015 13:32:49 GMT

Hi Sunny,

Thanks for your response.

Your expression display data from the current Month and current Year only, take a look:

Re: Working with the function Date#

sunny_talwar — Mon, 23 Nov 2015 13:37:06 GMT

Did you change AddMonths to AddYears? -> AddYears(Today(), -1)

=If([Data do Resultado] >= AddYears(Today(), -1) and [Data do Resultado] <= Today(), [Data do Resultado])

Re: Working with the function Date#

tamilarasu — Mon, 23 Nov 2015 13:40:39 GMT

You can also try,

if([Data do Resultado]>=AddMonths(today(),-12) and [Data do Resultado]<= date(today(),'MMM/YYYY'),[Data do Resultado])

Re: Working with the function Date#

Mon, 23 Nov 2015 13:40:42 GMT

Yes Sunny, i changed AddMonths to AddYears

Re: Working with the function Date#

sunny_talwar — Mon, 23 Nov 2015 13:42:54 GMT

Two things to check?

1) Do you have data for the period between Nov-14 till Oct-15?

2) Have you made selections which has removed any data you might have for Nov-14 till Oct-15?

Re: Working with the function Date#

Anonymous — Mon, 23 Nov 2015 13:47:59 GMT

Hi,

You should modify your expression and add a set analyses: {<[Data do Resultado]={">=$(=AddMonths(today(),-12))"}>}

Exemple: sum({<[Data do Resultado]={">=$(=AddMonths(today(),-12))"}>} Sales)

and for your dimension you can simply use the function:

Month([Data do Resultado])&'/'&Year([Data do Resultado])

without any filter as you set analyses will do it.

Hope this works

Re: Working with the function Date#

Anonymous — Mon, 23 Nov 2015 13:57:00 GMT

Hi,

You should modify your expression and add a set analyses: {<[Data do Resultado]={">=$(=AddMonths(today(),-12))"}>}

Exemple: sum({<[Data do Resultado]={">=$(=AddMonths(today(),-12))"}>} Sales)

and for your dimension you can simply use the function:

Month([Data do Resultado])&'/'&Year([Data do Resultado])

without any filter as you set analyses will do it.

Hope this works

Re: Working with the function Date#

thomaslg_wq — Mon, 23 Nov 2015 13:58:54 GMT

Hi,

1 : You could set your dimension as iif([Data do Resultado]>=AddMonths(today(),-12) and [Data do Resultado]<= date(today(),'MMM/YYYY'),Month([Data do Resultado])&'-'&Year([Data do Resultado]))

2 : You could use a master calendar with Period ID in your data model and use a set analysis in your expression:

- You'll get every period an ID as yearmonth1 = 1 and yearmonth13 = 13 in your data model,

- and your expression would be as sum( {<PeriodID={">=$(=max(PeriodID)-12)"}>} #valuefield)

Your dimension would be YearMonth of master calendar.

Hope it helps

Thomas

Re: Working with the function Date#

Mon, 23 Nov 2015 15:36:39 GMT

Hi Sunny,

You were right, i forgot to check if there was data for the period between Nov/2014 till Oct/2015.

I input data into this period, than cleared all filters, and thats the result:

As you can see, each month is duplicated.

Do you know why this is happening?

Thanks

Re: Working with the function Date#

sunny_talwar — Mon, 23 Nov 2015 15:38:13 GMT

Try this:

=If([Data do Resultado] >= AddYears(Today(), -1) and [Data do Resultado] <= Today(), MonthName([Data do Resultado]))

Re: Working with the function Date#

Marcio_Campestrini — Mon, 23 Nov 2015 16:03:24 GMT

Hi Rodrigo,

I faced a situation like yours, and I just created an information about month/yyyy with this instruction:

Year([Data do Resultado]) as Year,
Month([Data do Resultado]) as Month,

Month([Data do Resultado]) & '/' & Year([Data do Resultado]) as YearMonth,

Márcio

Re: Working with the function Date#

Mon, 23 Nov 2015 16:13:27 GMT

Hi rodrigo

why don't you use the dimension limit (limited to 12 months) ?

best regards

chris