topic Re: Show latest 12 months date in QlikView

Show latest 12 months date

udaya_kumar — Wed, 18 Nov 2015 05:41:31 GMT

Hi,

I have date field which has dates like Jan-14, Feb-14, Mar-14......... Dec-15 and the data keeps increasing like Jan-16, Feb-16 and so on.

I have to show always latest 12 months date in the chart and tables.

How to achieve this condition?

Re: Show latest 12 months date

arulsettu — Wed, 18 Nov 2015 05:44:20 GMT

try this

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today(), -11))<=$(=Today())’}>} Sales )

for more check this link

Set Analysis for certain Point in Time

Re: Show latest 12 months date

Anonymous — Wed, 18 Nov 2015 05:46:54 GMT

see these threads:

Show past 12 months

How to display Last 12 Month in Dimension in Bar Chart

Displaying last 12 months trend

Make sure format should be same for everyone.

Re: Show latest 12 months date

Gysbert_Wassenaar — Wed, 18 Nov 2015 05:54:21 GMT

It depends on what your 'date' field really contains. You need a date field with real dates or numbers to be able to calculated a maximum value to retrieve the latest value. Once you have that you can calculate what the latest 12 months are.

In this load statement a date field is created from a field that contains text values like 'Jan-14'.

LOAD

... some fields...,

date(date#(MyDate,'MMM-YY'),'MMM-YY') as MyDate,

....some other fields...

FROM ...

Now that MyDate is a real date you can use it in expressions to make the chart show the latest 12 months. Suppose you have an expression in your chart like sum(Sales). To make sure the charts shows only the information of the latest 12 months you add a records set definition to it:

sum({<MyDate={">$(=AddMonths(Max(MyDate),-12))"}>}Sales).

Max(MyDate) calculates the latest month. AddMonths(...,-12) then calculates the MyDate value that's 12 months earlier. And the larger then operator {"> ..."} is used to filter the field MyDate to select only those MyDate values that are larger than the value that's 12 months before the maximum, i.e. latest, MyDate value.