Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this:
=If([Data do Resultado] >= AddYears(Today(), -1) and [Data do Resultado] <= Today(), MonthName([Data do Resultado]))
Not sure, but try this may be:
=If([Data do Resultado] >= AddYears(Today(), -1) and [Data do Resultado] <= Today(), [Data do Resultado])
Hi Sunny,
Thanks for your response.
Your expression display data from the current Month and current Year only, take a look:
Did you change AddMonths to AddYears? -> AddYears(Today(), -1)
=If([Data do Resultado] >= AddYears(Today(), -1) and [Data do Resultado] <= Today(), [Data do Resultado])
You can also try,
if([Data do Resultado]>=AddMonths(today(),-12) and [Data do Resultado]<= date(today(),'MMM/YYYY'),[Data do Resultado])
Yes Sunny, i changed AddMonths to AddYears
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?
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
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
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