Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an expression in my tool that looks at cumulative percent of a code, and then assigns a value of A, B, or C based on that volume.
Something like the below, except Cumluative Perent is in itself a calculation.
If(CumulativePercent<=.8,'A', if(CumulativePercent<=.95,'B',C))
This works great.
However, I want to be able to create a filter that a user can select, to show only A, B, or C codes (or some combination thereof).
Additionally, if the user click on B in the straight table, instead of giving me all instances of "b", it gives me just that one item.
Is there any way to create that filter and fix the table?
Thanks, Melanie
You can ignore filters within set analysis, by using the syntax sum({<{Year=,Month=,Date={'$(SelectedDate)'}>} Field). Set analysis is quite complicated, but there are plenty of references to get you started. However, in this instance the expression you are adding into the list box is not a field that can be manipulated (like Year and Month are in the example).
If you think the value of your calculation, and it's relationship to the various datapoints will not change when filtering happens, then putting the calculation into a script is worthwhile. What you may be able to do is set up a flag that is set up A, B or C.
If you don't want to send out the full application you are building, perhaps you can send the portion of the script in which the RollingSales, TotalSales and Product/Item are defined. From this I may be able to advise you on how to set up such a flag.
Jonathan
The Item and TotalSales2 are coming in from a SQL table, which I swapped for an inline load.
All that is needed for this calculation is item codes and their total sales.
I did include how RollingSales was calculated see below:
In the script:
I sorted the data by TotalSales2 decending. Then created a cumulative sum of TotalSales2 called RollingSales.
In the chart:
I then created an expression called Cumulative%, which is the RollingSum divided by the Sum Total of all TotalSales2 = RollingSales/sum(Total(TotalSales2))
I then created a second expression called ABC which looked at the Cumulative% and assigned a value of A, B or C depending on an if statement as follows: if((RollingSales/sum(Total(TotalSales2)))<=.8,'A',if((RollingSales/sum(Total(TotalSales2)))<=.95,'B','C'))
However, the above ABC expression changes depending on filters. It would be easiest (I believe) if this calculation was done in the script, but I'm having trouble with the "Total" function which I cannot get to work.
Hope this clarifies, Melanie
I think you would like to do something like this in the script:
1. Load sales data into Sales table
2. Load products into Products table
3. Left join onto Sales table sum of all sales
4. Left join onto Products table sum of Sales value for each product
5. Left join onto Sales table sum of Sales value for all products from step 4, for each product
6. Left join onto Products table sum of Product Sales divided by sum of all products
7. Left join onto Sales table ABC flag based on calculation from step 6
Left joins where you sum up sales values need to include a GROUP BY clause, to say what you are summing up 'by'. Syntax for some of these steps could be as follows:
3. Left join onto Sales table sum of all sales
LEFT JOIN (Sales) LOAD
SalesKey, // A unique identifier of each sales record
sum(SalesValue) AS Sales_TotalSalesValue
RESIDENT Sales;
4. Left join onto Products table sum of Sales value for each product
LEFT JOIN (Products) LOAD
ProductKey, // The key of the Product that exists on the Sales and Product tables
sum(SalesValue) AS Product_TotalSalesValue
RESIDENT Sales
GROUP BY ProductKey;
5. Left join onto Sales table sum of Sales value for all products from step 4, for each product
LEFT JOIN (Sales) LOAD
ProductKey,
Product_TotalSalesValue AS Sales_TotalProductSalesValue
RESIDENT Products;
6. Left join onto Sales table sum of Product Sales divided by sum of all products, as well as the ABC flag
LEFT JOIN (Sales) LOAD
SalesKey,
Sales_TotalProductSalesValue/Sales_TotalSalesValue AS Sales_Calc
RESIDENT Products;
7. Left join onto Sales table ABC flag based on calculation
LEFT JOIN (Sales) LOAD
SalesKey,
if(Fractile(Sales_Calc,0.8),'A',if(Fractile(Sales_Calc,0.95),'B','C')) AS Sales_CalcFilter
RESIDENT Products;
I hope this is helpful. You will have to tweak it of course, but if it doesn't suit your purposes you might want to repost your question (as seeing this many responses sometimes prevents members viewing posts)
Jonathan
I don't believe you use on-the-fly calculation results as the criteria for a filter.
Try to set "Searchable" for the column (Properties->Presentation). This way user will have to enter some substring for searching and as result of search proper filters will be set on dimensions. It is not so convienent as in case of just cklicking on the value of interest, but you can achieve a goal of filtering the records you are interested in.