Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Are you using this as a calculated dimension?
Hi,
Set two Variable :
vMonthStartSelected = AddMonths(vMonthEndSelected, -5)
vMonthEndSelected = max(Month End Date)
use above variable in expression :
Sum({<DATE={'>=$(vMonthStartSelected )<=$(vMonthEndSelected )'}>} [Measure Name])
In dimension use Month.
yes sunny
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
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.
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.
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)
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)