I have 2 fields 'Month' and 'ID' in a straight table. The field 'Month' has 60 months in it in MM-YYYY format. How can I get the latest 24 months in DD-MM-YYYY format?

[#1]

I have 2 fields 'Month' and 'ID' in a straight table. The field 'Month' has 60 months in it in MM-YYYY format. How can I get the latest 24 months in DD-MM-YYYY format?

[Anonymous]

Script:

ExampleData:

Load

    *,

    Date (MakeDate(right(CustomerDate,4),left(CustomerDate,2),1),'DD-MM-YYYY') as FormattedDate

Inline

[

CustomerID, CustomerDate, CustomerSales

1,01-2012,3

2,10-2014,6

3,04-2009,2

4,11-2015,7

5,03-2013,1

];

Note that the script converts a string in MM-YYYY format to a QlikView date in DD-MM-YYYY format. The script will show each month as the first day of the month since a specific day of the month isn't available. If your source date is in date format, no need to worry about this step.

Create a new chart with two dimensions, FormattedDate and CustomerID.

The expression doesn't matter, but here it's sum (CustomerSales).

To show only the most recent few months:

1. In the Sort sheet promote FormattedDate to the top or first position. Sort in descending order by Numeric Value. QlikView stores days as numbers, so by converting above we're make sure that 11-2013 comes after 2-2013 (which wouldn't happen if they were text).

2. Now go to Dimension Limits, select FormattedDate, click "Restrict which values are displayed using the first expression," Show only, change to First, and then enter 3 in the number of values.

You end up with:

For your actual use, you'll want to replace first 3 with first 24.

[psankepalli]

Try this formula at Expression.

DATE={'>=$(=(date(AddMonths(date#(max(DATE),'MM-YYYY'),-24),'MMM-YY')))'}

Thanks

Kesav

[tresesco]

Could you share your sample qvw ?

[Anonymous]

Hello,

does the following expression work for you?:

date#('01-'&month, 'DD-MM-YYYY')

[miguelbraga]

Hey there,

You need to show in that format with your required format and leave the rest with the old format? Or do you want exclude the data before the 24 months and only consider the last 24 months with your required format?

Thanks,

MB