How to calculate Sales Weight

[sogloqlik]

Hello everyone,

I received a requirement to calculate weighted availability, meaning:

The percentage of total sales in the stores where the selected product is sold, out of the total sales across all stores.

The expression needs to be displayed in two charts:

1. A Bar chart with Sector as the dimension.

2. A Line chart with Sector and Year as dimensions.

I was able to create the bar chart using the following expression:

({1<[Material]=P({1<[Product_Division]={'10','12','13','16','26','30','35'}>}[Material]), CustKey=P({<[Customers.Name] -= {"*Marlog*"}, [Customer_H6_Desc.]={'Private'}>} CustKey),
, Year = $::Year>}

[Sales])
/
{1< [Material]=P({1<[Product_Division]={'10','12','13','16','26','30','35','40'}>}[Material]), CustKey = P({1<[Customers.Name] -= {"*Marlog*"}, [Customer_H6_Desc.]={'Private'}>}CustKey), Year = $::Year>}
[Sales]

However, I was not able to make it work properly for the line chart.

There are Three additional constraints:

• [Sales] is already a master measure and cannot be modified.

• The line chart should ignore Year selection (So the trend will always be visible).

• There may be selections on different product levels (Category, Barcode, etc.).

•  

I would appreciate a solution or an alternative expression that might work better in this scenario.

Thanks in advance!

[Chanty4u]

May be try this 

Sum(

{<

    Year=,

    Material = P({<[Product_Division]={'10','12','13','16','26','30','35'}>} Material),

    CustKey = P({<[Customers.Name] -= {"*Marlog*"}, [Customer_H6_Desc.]={'Private'}>} CustKey)

>}

[Sales]

)

/

Sum(

{<

    Year=,

    CustKey = P({<[Customers.Name] -= {"*Marlog*"}, [Customer_H6_Desc.]={'Private'}>} CustKey)

>}

TOTAL <Sector,Year> [Sales]

)

[sogloqlik]

Hi Chanty4u,

Thank you for your answer.

As i wrote in my original post, Sales is alreay a measure (With Sum) and nested aggrigation is not allowed.

[marcus_sommer]

I suggest to use a table-chart with both dimensions and both expression-parts in parallel. The aim of it is to see which parts are working as expected and which not. And the not working ones may then splitted into n further parts.

Next steps may be to integrate only the master-measure and/or parts of it as further expressions. These reduced expressions could be the starting point for some adjustments and/or wrapping them with further aggregations by using some aggr() constructs.

The simplified complexity of such an approach is usually much more helpful as the efforts for n extra steps.

[sogloqlik]

Hi,

Already done that, the issue is the numerator but I Havn't managed to solve the issue.

[marcus_sommer]

Try to play with aggr() and even more simplified expressions to comprehend how the general logic is working. Means attempts like:

sum(Sales)
sum(aggr(sum(Sales), Sector))
sum(aggr(sum(Sales), Sector, Year))

whereby Sales means the origin field and not the master-measure. The first steps don't need any conditions - they could be added later again step by step.

[sogloqlik]

Hi,

I appriciate your help but was hoping for more of a final answer since i had done all of these steps before posting.

The issue is that becase the set analysis is calculated per chart, it does not consider the dimansion (Year) value. you were right about using Aggr but somehow it didn't solve it.

[marcus_sommer]

There is a misunderstanding in the way how the data interact with each other.

A set analysis doesn't work per object else globally against the selection state respectively n alternate states - and it is always a selection. It won't influence the relationship between the data.

An aggr() could be used to create a new not existing relationship between the data - even connecting not related data in a cartesian way would be possible. This feature is very powerful but it has also some complexity and performance-impact. It's not really sensible to apply it for things which could be done within the data-model.

Beside this be aware that your set analysis connects two states - Year = $::Year - which may conflicting with the states in the master-measure and which may also be considered within an aggr-approach.

[sogloqlik]

Hi,

First of all, thank you again for your patience.

I decided to drop the measure-based approach and instead work directly with the field itself. I came up with two simple alternatives:

Sum({1< [Material]=, [Customer] = P([Customer]) >} Sales)

Sum({< [Material]=, Category=, [Customer] = P([Customer]) >} Sales)
(This option avoids using {1} and still allows filtering fields such as Category.)

When I filter both Year and Category, both expressions return the expected results.

However, when I filter only Category, the calculation seems to include all customers who ever bought a product from that category, and then performs the aggregation across all years.

What I actually need is for the expression to evaluate the customers separately for each year when no Year filter is applied.

What would be the correct way to adjust the set analysis so that the customer set is calculated per year, even when Year is not explicitly selected?

[marcus_sommer · Accepted solution]

In the most scenarios a statement like: ... Field = p(Field) ... isn't necessary because it's just transferring the selection state which is already by default considered. But if the p() itself get a set statement like: ... Field = p({ SET } Field) ... it would be possible to define more complex selection states.

I could imagine that your expression may go in such direction:

sum(aggr(Sum({< [Material]=, Category= >} Sales), [Customer], [Year]))

which calculates a sales-result per customer and year.