# QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

New Contributor II

## how to show latest 5 months of data dynamically?

Hi Community,

I have a requirement that i need to show the latest 5 months of data,here is the formula that am using that for that

= IF([Month End Date]>=\$(#=Date(Max({1<Month_End_Date={"<=\$(Date(vMonthEndSelected))"}>} Month_End_Date,5),'DD MMM YY'))

AND [Month End Date]<=Date(Date#('\$(vMonthEndSelected)','DD MMM YY'))

,[Month End Date]

Here vMonthEndSelected=max(Month End Date)

But am unable to to show the latest 5 months of data dynamically.any one can help me where exactly the problem is please.

Thanks

12 Replies
Contributor

## Re: how to show latest 5 months of data dynamically?

I've notivce that (at least some times) Set Analysis on date records is tricky...

It seems to me that set analysis is not handling pretty well DUAL data sets.

Therefore, what I've found it's always working: create an additional column of data that is only the NUMERIC part (or define your own as a numerical one).

More specifically to your set of data, I would create a new column in the script, such as

 num([Month End Date]) as [Month End Date Num]

and use the new column in the interface formulas instead of the old one.

Hope it helps

MVP

## Re: how to show latest 5 months of data dynamically?

Are you using this as a calculated dimension?

New Contributor III

## Re: how to show latest 5 months of data dynamically?

Hi,

Set two Variable :

vMonthEndSelected = max(Month End Date)

use above variable in expression :

Sum({<DATE={'>=\$(vMonthStartSelected )<=\$(vMonthEndSelected )'}>} [Measure Name])

In dimension use Month.

New Contributor II

yes sunny

MVP

## Re: how to show latest 5 months of data dynamically?

You don't you use set analysis instead in your expression? I think set analysis tend to be more efficient in comparison to calculated dimensions. If you are able to provide me with your current expression, I might be able to give you something to try

New Contributor II

## Re: how to show latest 5 months of data dynamically?

here is the expressions am using

Sum({\$<Product_Type={"X"},Category={"A"},Name={"Z1"}>}sales_amount)

Sum({\$<Product_Type={"X"},Category={"A"},Name={"Z2"}>}sales_amount)

i want months in X-axis.

MVP

## Re: how to show latest 5 months of data dynamically?

Try this:

Sum({\$<Product_Type={"X"},Category={"A"},Name={"Z1"}, [Month End Date] = {"\$(='>' & Date(AddMonths(Max([Month End Date]), -5), 'DD MMM YY) & '<=' & Date(Max([Month End Date]), 'DD MMM YY'))"}>} sales_amount)

Sum({\$<Product_Type={"X"},Category={"A"},Name={"Z2"}, [Month End Date] = {"\$(='>' & Date(AddMonths(Max([Month End Date]), -5), 'DD MMM YY) & '<=' & Date(Max([Month End Date]), 'DD MMM YY'))"}>} sales_amount)

Update: Dimension would just be a somple [Month End Date] field.

MVP

## Re: how to show latest 5 months of data dynamically?

Or try this as your calculated dimension:

=If([Month End Date] >= AddMonths(Max(TOTAL Month_End_Date), -5) and [Month End Date] <= Max(TOTAL Month_End_Date), [Month End Date])

Expressions:

Sum({\$<Product_Type={"X"},Category={"A"},Name={"Z1"}, [Month End Date]>}sales_amount)

Sum({\$<Product_Type={"X"},Category={"A"},Name={"Z2"}, [Month End Date]>}sales_amount)

New Contributor II

## Re: how to show latest 5 months of data dynamically?

thanks for your reply if i do this its not responding though i change the months.

=If([Month End Date] >= AddMonths(Max(TOTAL Month_End_Date), -5) and [Month End Date] <=Max(TOTAL Month_End_Date), [Month End Date])

Expressions:

Sum({\$<Product_Type={"X"},Category={"A"},Name={"Z1"}, [Month End Date]>}sales_amount)

Sum({\$<Product_Type={"X"},Category={"A"},Name={"Z2"}, [Month End Date]>}sales_amount)