topic Re: How to show data from the 12 previous months only? in QlikView

How to show data from the 12 previous months only?

Wed, 11 Nov 2015 17:11:55 GMT

Hello everyone,

I have a pivot table in my sheet, where i need to show data only of the previous 12 months.

The date field of my data base is like that:

Thanks

Re: How to show data from the 12 previous months only?

Wed, 11 Nov 2015 17:17:39 GMT

in your expression, use set analysis

you can define a variable as today - 12 months using the addmonths function (use -12) and then that variable can then be used in your set analysis statement as part of the expression

example of variable - vDateRange

=('>=' & AddMonths(max(MyDate), -12 , 0 ) & '<=' & Date(max(MyDate)))

in set analysis you are only summing up inforamtion if the date of the item (keydate) is between the max date in your application and 12 months back

In set analys

Sum({$< Keydate = {"$(vDateRange)"} >} DimesionName you are summing)])

Re: How to show data from the 12 previous months only?

engishfaque — Thu, 12 Nov 2015 05:38:43 GMT

Dear Rodrigo,

Expression:

=Sum({<Year = {$(=Max(Year))}, Quarter =, Date =, Month = {$(=Max(Month)-12)}>} YourFieldName)

Kind regards,

Ishfaque Ahmed

Re: How to show data from the 12 previous months only?

anlonghi2 — Thu, 12 Nov 2015 07:11:06 GMT

Hi Rodrigo,

if you use "Data of resultado" as dimension try using a calculated dimension instead.it.

Eg.: if("Data of resultado">=addmonths(today(),-12), "Data of resultado")

and then check "Suppress when value is null".

Best regards

Andrea

Re: How to show data from the 12 previous months only?

jagan — Thu, 12 Nov 2015 07:26:02 GMT

Hi,

check this link

Set Analysis for certain Point in Time

Re: How to show data from the 12 previous months only?

Mon, 16 Nov 2015 11:53:03 GMT

Hi Ishfaque,

Thanks for your answer!

In my case, i am not doing a sum, i just want to show the current value of a field.

So, how it shoud be my expression??

Thanks

Re: How to show data from the 12 previous months only?

Chanty4u — Mon, 16 Nov 2015 11:58:45 GMT

chk below link

How to create MTD,YTD,PMTD and PYTD

hope this helps you

Thanks

Suresh

Re: How to show data from the 12 previous months only?

Mon, 16 Nov 2015 13:33:53 GMT

Hi Anlonghi2,

Thanks for your tip.

I did what you suggested, but did´t work, no results was shown in the table.

The field "Data do Resultado" is a date field in the following format: MM/YYYY.

In the script, i did the following:

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

I added the following calculated dimension:

if("Month">=AddMonths(Today(),-12), "Month")

After apply that expression, my table displayed no results, all blank.

Re: How to show data from the 12 previous months only?

anlonghi2 — Tue, 17 Nov 2015 08:16:53 GMT

Hi Rodrigo,

please look at attached qlik doc.

Best regards

Andrea

Re: How to show data from the 12 previous months only?

Tue, 17 Nov 2015 12:41:48 GMT

Hi Andrea,

Thanks for your help.

I did exactly how you made in the the qlik doc attached, and it was displayed all months of my data base, take a look:

I dont know why it didnt limited in 12 months.

You can see that some months are duplicated, when the result of green or red are different.

Re: How to show data from the 12 previous months only?

anlonghi2 — Tue, 17 Nov 2015 18:49:43 GMT

Hi Rodrigo,

please look at attached qvd where I used Group field to simulate your situation.

I made a little change to the calculated dimension.

Please let me know

Best regards

Andrea