Working with the function Date#

[#1]

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.

[#2]

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

[sunny_talwar · Accepted solution]

Try this:

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

[Marcio_Campestrini]

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

[#5]

Hi rodrigo

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

best regards

chris