First of all I had the following problem with a pivot table, I have real sales data by country for different month: each row was a different country. The total was supossed to be the total for all countries but the total result was the max value of each row not the sum of rows. For example:
Instead of :
The solutions was the following: use AGGR(SET ANALYSIS)Country, Month
I had the same problem for Graphs where I only had Month dimension, I triedd wit set anlysis, aggr function, total function but the total only work for simple realt data but it is not right for complex forecast data because the total that appears is the total of Spain.
It is not possible to use aggr function and set analysis for a graph with complex data as a forecast YTD average for each month (Fact1/Fact2). Any solution?
There was a solution for bar graph (not fore line graph). Use Month dimension and Country dimension: show the total like: France, Spain and Total. Then I colour blanck thedimension Country and the different countries so I can show only Total. The problem of using this is that the format of the graph have change: Now I have stacking bars instead of one bar for each calculated fact.
So I couldn't make possible a Forecast YTD graph. The question are the following.
1.Is it not possible to use aggr function and set analysis for a graph with complex data as a forecast YTD average for each month. The calculated fact is like (Fact/Fact1) / (Fact2/Fact3).- Fact2/Fact3).- Any solution? I tried a lot of times and it doesn't works. How can I use total or dimesionality in it?
I only could make possible the total of one Country whit this formula: