Set Analysis

[LuisM]

Hi 🙂

I want to present a KPI of the Budget but show the max month of 2022....for example January-June so l have this set analysis:  

Sum({<Month={$(=Max(Month))}>} [Sales Budget HQ])

but it doesnt show me the correct amount, the set analysis show me the Total Amount

The correct amount that should show me is this: month by month the amount

What is wrong in my set analysis? 

I hope can help me guys

Thanks u all

[Andrei_Cusnir]

Hello,

Try using the following expression:

Sum({<Month={"$(=Month(Max(Month)))"}>}Budget)

I am not 100% sure how you created the field Month. However, keep in mind that:

1. Max(Month) will return values 1,2,3... and NOT ENE, FEB, MAR... etc.

2. Therefore, set analysis will most probably fail again since 1 is not equal with ENE etc.

In my case I have created the filed Month with:

Month(Date) as Month

In Data Load editor. Therefore I have the following dataset:

Hence my KPI shows for all values:

And for max month (Using expression above):

You can take this implementation in consideration and modify accordingly. 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

[LuisM]

Hi,

This is another model that l did with the same set analysis but using YEAR: 

Sum({<Year={$(=Max(Year))}>} [Sales Budget HQ])

Sales YTD is until may, so the sum of budget is until may too, if l filter by January, budget and sales move amount too, thats what l want to show in my report:

In my other model if l change the month, sales move correct but Budget amount no, summarize all months, I tried with your expression but still not showing the correct amount 

what could be wrong? When l work with one .xlsm works ok the set analysis, but when l work with a connection to a SQL server is when not move the correct amounts

Thanks

[Andrei_Cusnir · Accepted solution]

Hello,

If you are using the exact same expression on the exact same dataset but it only works when the dataset is loaded from .xlsm and doesn't work when the data is loaded from SQL server, then the issue is not with the expression, but with the loaded data type. 

For example, in one source, the data might be defined with specific format, that is different from the SQL server, or the same format might be interpreted a bit differently from source to source. Maybe when you load data from one resource, is interpreted as dual value and when you load it from the other resource it is interpreted as string, number etc. My recommendation would be:

1. Keep the expression that works for you in .xlsm 
2. Try loading the data from .xlsm via Data load editor and use functions to apply a specific format on your loaded data, even if the format is the same as if you load it directly without functions. For example, if you load Number as Number and the expression works, try Num(Number) as Number or use Dual() function for the months etc.
3. If you confirm that the expression still works and returns the correct result, use the exact same load statement on the values loaded from SQL Server. This will ensure that the loaded values are interpreted with the exact same way as the .xmls and it will ensure that the expression will work there as well.
4. NOTE: The loaded dataset values might not change with the modifications, but you will enforce a format to be applied, that you know should work with the given expression.

I hope that this information is helpful.