Hello everyone,
I have been experimenting with the P() function and have found it fantastic so far. Currently, it is being used alongside an Alternate State to get the total sales by customers when a specific product is selected - heard this referred to as the Halo-effect as it shows the other products which would have been bought alongside the selected product.
I've run into a challenge when trying to get a chart to calculate the total p() value as this requires an expression to be calculated once per dimension, as opposed to once per the chart.
In the example attached - if you select an idProduct in the Inherited State, the expressions in the Alternate State will give you the total sales for customers which purchased that product (i.e. sales including other products which that customer bought).
What I need to get is the total sales of customers' orders by the idProduct, including where these products would cross-over - as below:
| idProduct | Total Customer Sales |
|---|
| 1 | 48 |
| 2 | 44 |
| 3 | 46 |
| 4 | 46 |
| 5 | 59 |
| 6 | 44 |
This table is essentially the values of the 'Total Customer Sales' field when going through the idProduct.
I'm aware that this can be done by loading the table again and changing all but the idCustomer field name - and that works perfectly in this example - but this is not viable in my main model which has many millions of records and the expressions are more complicated than just a Sum(Sales).
I've tried using the Aggr() function, although this doesn't seem to work due to the different states and that QlikView still doesn't like using the same record twice in a chart.
Does anyone know of any way to get an expression to calculate separately for each field in the dimension?
Any help would be greatly appreciated.
