Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have a pivot table object on my dashboard with 2 dimensions: Country name and Month-Year.
So for example, the table looks like this.
Country | Month-Year | Total sales |
---|---|---|
Brazil | Oct-2015 | 150,000 |
Brazil | Nov-2015 | 175,000 |
Brazil | Dec-2015 | 210,000 |
The problem is I only want to display the latest month-year figure (Dec-2015) for a country in the example above. Obviously I can do this manually by selecting a Month-Year by clicking on a date, but is there any way I can apply set analysis to a dimension (Month-Year) to restrict what rows are displayed automatically?
Or can I only do this in the script ?
Thanks
MV
For same, create a monthnumber at script like this?
Year*12+month(Date) as Month
Then use my above expression
may be replacing your Total Sales expression with this?
=sum({<Month={'=$('=max(Month)')'}>} Sales)
You can restrict the dimension by applying set analysis to your expressions:
Assuming you have Sum(Sales), change it to -> Sum({<Month-Year = {"$(=Date(Max(Month-Year), 'MMM-YYYY'))"}>}Sales)
For same, create a monthnumber at script like this?
Year*12+month(Date) as Month
Then use my above expression
Hei hope this work
calculated dimension will be if(Month-Year=month(Today())&'-'&Year(Today()),Month-Year,null())
and check suppress when Value is Null in dimension Tab.
Many thanks Balraj