Set analyzis in evaluation of product mix

[#1]

I'm trying to evaluate mix of products.

I prepare pivot table with Salesman, Client number, main product group, upper product group and product group

The idea of mix is:

there is a percent of value the individual products comapring to the total sales for indivdual client

[Value]/(sum({$<[Product group]=, [Main product group]=, [Upper product group]=>} Total<[Client name]> Value)/1000)

I put the $ to the expresins because users can change the date of the analysis. But this expressions doesn't work properly.

Please help

[Miguel_Angel_Baeyens]

Hello,

Are you taking into account that

Sum({$<[Product group]=>} Value)

means "Sum Value of everything but [Product Group]" (excluding sum of values that correspond to [Product Group] selection)?

The issue is assign some variable to [Product Group], [Main Product Group] and so. Which variable do you want to assign to those sets?

[#3]

the problem is that i need all 3 variable [Main product group], [Upper product group], [Product group]. We have got over few houndred product groups so users need to have a possibility to narrow down them.

next i need total of value of client name or number - if I skip this i will have total sales

[Miguel_Angel_Baeyens]

Magda wrote:the problem is that i need all 3 variable [Main product group], [Upper product group], [Product group]. We have got over few houndred product groups so users need to have a possibility to narrow down them.

You may then need a listbox so your users can narrow the search by selecting the items you want to show in the listbox. You don't need any set analysis in this case, because you will always get totals regarding your selection. The only meaning I could understand to use set analysis as you mention is to keep some of the [Main product group], [Upper product group], [Product group] regardless your users selection. If you want them to be changed by users, don't use set analysis or set it up with listboxes.

[#5 · Accepted solution]

I resolved the problem using Jason's Lang aplications - YTD calculations using Set Analysis

Proper expresions is:

[Value]/(sum({$<[Product group]={"*"}, [Main product group]={"*"}, [Upper product group]={"*"}>} Total<[Client name]> Value)/1000)

Thanks a lot for your helping