QLIKSENSE Filter Last 12 months

[qlikuser22]

Hi 

I have a field name as DATE and I am generating a report in PPTx format. I am working in QLIK SENSE Nprinting.

I want to filter last 12 months data. i.e, From current month(eg June 2021) to last year July 2020. 

I tried the expression in Advanced Search : =([Date]>AddYear(Today(),-1))*([Date]<=Today()). But this is not working. It says failed to create filters. 

I tried the solution already available in Qlik Community. Still it doesn't work. 

Thanks in advance. 

[Ruggero_Piccoli]

Hi,

I suggest you to test the advanced search formula in Qlik Sense before creating a Qlik NPrinting filter with it. 

The Qlik Sense function is AddYears, not AddYear. Refer to https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

Best Regards,

Ruggero

[Lech_Miszkiewicz]

On top of the error in your expression which Ruggero picked up please remember that Qlik is case sensitive. 

You have written that you have field called "DATE" but in your filter you use field called "Date" so which one is correct?

[qlikuser22]

Thanks for your replies. I tried all possibility but I am not able to filter last 12 months in qliksense Nprinintg. I will explain my scenario again. I want to filter previous 12 months data (i.e., Jun 2018 to Jun 2017 backwards). I am not allowed to make alterations in qlik load scripts. I should use only the advanced search option to filter in Nprinitng. I am taking care of the case sensitive. I tried the following expresssions,

1. =([Date]>AddYear(Today(),-1))*([Date]<=Today())

2.  =([Date]>AddYears(Today(),-1))*([Date]<=Today())

3.  =year(AddMonths(now(),-12))

4. =Month(AddMonths(Now(), - 12))

Nothing helped me to get the solution. I get error like Not able to apply filter or I get an output without anything filtered. 

Kindly help.

Iam using Qliksense Nprinting. 

[Ruggero_Piccoli · Accepted solution]

Hi,

It is not needed to change the Qlik Sense app reload script. You can test your formula by using Qlik Sense advanced search directly in the app interface. For example:

This is your formula no. 2 adapted to my copy of the Qlik Sense Beginners app. I changed the formula until I had the values I need. When you have the formula ready you can copy it in the Qlik NPrinting advanced search.

Best Regards,

Ruggero

[qlikuser22]

Hi Ruggero

I understand your answer. But I have my field as (example : 2020-06-21) . I have used the formula =Left(Text(Date),7) to extract the months. Now i use the formula in Nprinting under advanced search filter as =([Date]>AddYears(Today(),-1))*([Date]<=Today()). But I am not able to obtain the filter result. Please help out. Need only last 12 months data to be filtered. 

[Ruggero_Piccoli]

Hi,

I have not understood, you field Date is a string?

What do you get if you apply the formula in the Qlik Sense app advanced search?

Best Regards,

Ruggero

[qlikuser22]

Hi. Yes my date field is a string.

If i apply this formula in advanaced search  =([Date]>AddYears(Today(),-1))*([Date]<=Today()). It is working well but there is a missing of few values if I cross verify with the qlik app. 

I also tried using  =([Date]>AddMonths(Now(),-12))*([Date]<=Now()). Here also i miss few values alone. Please help. 

[Ruggero_Piccoli]

Hi,

I'm not understanding. The Qlik Sense function AddYears() returns a date in dual format https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFun... but you told that the Date field is a string. 

When you say " missing of few values" do you mean that in the report generated by Qlik NPrinting there are less records than applying the same formula in the same Qlik Sense app used as a source? This is not possible, so check if you are applying other filters on the Qlik NPrinting side (user, report, object or task filters). Or are you filtering other fields on the same Qlik NPrinting filter? Applying the same filter in the Qlik Sense app used as source and in Qlik NPrinting must return the same results. 

Can you share a copy of the Qlik Sense app with only the Date field so we can try to reproduce? Also the list of missing values will help.

Best Regards,

Ruggero

[qlikuser22]

Thanks for the support. The issue is fixed and I can filter last 12 months. !!