How to show data from the 12 previous months only?

[#1]

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

[#2]

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

[engishfaque]

Dear Rodrigo,

Expression:

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

Kind regards,

Ishfaque Ahmed

[anlonghi2]

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

[jagan]

Hi,

check this link

Set Analysis for certain Point in Time

[#6]

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

[Chanty4u]

chk below link

How to create MTD,YTD,PMTD and PYTD

hope this helps you

Thanks

Suresh

[#8]

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.

[anlonghi2]

Hi Rodrigo,

please look at attached qlik doc.

Best regards

Andrea

[#10]

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.