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.

[sunny_talwar]

Not sure, but try this may be:

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

[#3]

Hi Sunny,

Thanks for your response.

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

[sunny_talwar]

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

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

[tamilarasu]

You can also try,

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

[#6]

Yes Sunny, i changed AddMonths to AddYears

[sunny_talwar]

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?

[Anonymous]

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

[Anonymous]

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

[thomaslg_wq]

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]))

OR

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