12 Replies Latest reply: Oct 20, 2016 5:51 AM by Prabir Adhikary

# 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

• ###### 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

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

Are you using this as a calculated dimension?

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

yes sunny

• ###### 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

• ###### 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.

• ###### 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.

• ###### 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.

• ###### 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)

• ###### 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)

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

Can you share screenshot of with and without selections?

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

here i have selected the june -16 but still its at the max(month).its not changing dynamically

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

Not entirely sure, but if you can share a sample, I can try looking into it.

Best,

Sunny