Difference between revisions of "ManagementBook"
m (Text replacement - "XLCubed" to "FluenceXL") |
|||
| (13 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | This is a sample Dashboard based on Bicycle Sales data which is included with the product installation. It connects to a simple local cube file - BicycleSales.cub. | + | This is a sample Dashboard based on Bicycle Sales data which is included with the product installation. It connects to a simple local cube file - [[BicycleSales_Demo_Cube|BicycleSales.cub]]. |
==User Selections== | ==User Selections== | ||
| − | + | User selections are through two slicers, on Month and PoS. The grids in the workbook select the slicer directly; the formulae are based on the cells which the slicer updates. | |
| − | [[image:managebooks1.png|centre| | + | [[image:managebooks1.png|centre|400px]] |
The ''Time Slicer'' updates $D$2, the ''PoS Slicer'' updates $G$2. | The ''Time Slicer'' updates $D$2, the ''PoS Slicer'' updates $G$2. | ||
| + | |||
| + | |||
==Dashboard Worksheet== | ==Dashboard Worksheet== | ||
| + | ''KPI table'' is in cells (B5:G13) | ||
| + | *Consists of an FluenceXL Grid (B5:D13) | ||
| + | **Grid header area is hidden in Grid Properties, Appearance | ||
| + | **Time and PoS are in the header area, and select the respective slicers | ||
| + | **Sparkline is based on selected month plus previous 11 members | ||
| + | *and FluenceXL cube and chart formulae (E5:G13) | ||
| + | **Variance % column uses an XL3Link() to allow the user to jump to detail on the selected KPI | ||
| + | **Bullet Graph in column G, built through an in-cell chart formula, is based on the Target YTD% | ||
| − | + | ''Region Table'' is in cells (B18:G24) | |
| + | *Consists of an FluenceXL grid (B18:F24), and an in-cell chart formula (G18) | ||
| + | *Time and PoS are in the header area, and select the respective slicers | ||
| + | ''Top 10 Regions / Products'' | ||
| + | *Xl3RankLookup and ValueRankLookup formulae | ||
| + | *Product also uses XL3Link() to jump to detail | ||
| − | + | ''Product Groups & HeatMap'' | |
| − | + | *Built from FluenceXL formula based data on hidden sheets | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | ==Variance Analysis Sheet== | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | [[image:managebooks2.png|centre|400px]] | |
| − | |||
| − | + | *Built using one FluenceXL grid, with embedded in-cell charting | |
| − | + | **It is restricted by the Pos and Time slicers | |
| − | + | **For Key Figures, it uses VarianceAnalysis!D3 , which is populated by the XL3Link formulae on the KPI table within the Dashboard sheet. | |
| − | + | **Contains an additional XL3Link in column E, contained within a grid calculation, to allow jump to detail on the selected product. | |
| − | |||
==Time Series Chart Sheet== | ==Time Series Chart Sheet== | ||
| − | This contains a detail table and chart of value and target for the selected period, product, PoS, and the Key figure passed in by the xl3Linkformulae in | + | This contains a detail table and chart of value and target for the selected period, product, PoS, and the Key figure passed in by the xl3Linkformulae in ''Variance Analysis''!$E$9: $E$29. |
Cell $O$8 is based on the selected month, and C:N use the XL3MemberNavigate() formula to choose the previous month.=, overall delivering a rolling 12 months. | Cell $O$8 is based on the selected month, and C:N use the XL3MemberNavigate() formula to choose the previous month.=, overall delivering a rolling 12 months. | ||
| − | Note that this could also be achieved in a grid, by driving the date hierarchy from $C$5 and choosing the | + | Note that this could also be achieved in a grid, by driving the date hierarchy from $C$5 and choosing the ''previous 11'' |
| + | |||
| + | == See Also == | ||
| + | * [[Slicers]] | ||
| + | * [[Formula Reference]] | ||
| + | |||
| + | |||
| + | [[Category:Formula Reporting]] | ||
| + | [[Category:Formulae]] | ||
Latest revision as of 09:29, 4 July 2023
This is a sample Dashboard based on Bicycle Sales data which is included with the product installation. It connects to a simple local cube file - BicycleSales.cub.
Contents
User Selections
User selections are through two slicers, on Month and PoS. The grids in the workbook select the slicer directly; the formulae are based on the cells which the slicer updates.
The Time Slicer updates $D$2, the PoS Slicer updates $G$2.
Dashboard Worksheet
KPI table is in cells (B5:G13)
- Consists of an FluenceXL Grid (B5:D13)
- Grid header area is hidden in Grid Properties, Appearance
- Time and PoS are in the header area, and select the respective slicers
- Sparkline is based on selected month plus previous 11 members
- and FluenceXL cube and chart formulae (E5:G13)
- Variance % column uses an XL3Link() to allow the user to jump to detail on the selected KPI
- Bullet Graph in column G, built through an in-cell chart formula, is based on the Target YTD%
Region Table is in cells (B18:G24)
- Consists of an FluenceXL grid (B18:F24), and an in-cell chart formula (G18)
- Time and PoS are in the header area, and select the respective slicers
Top 10 Regions / Products
- Xl3RankLookup and ValueRankLookup formulae
- Product also uses XL3Link() to jump to detail
Product Groups & HeatMap
- Built from FluenceXL formula based data on hidden sheets
Variance Analysis Sheet
- Built using one FluenceXL grid, with embedded in-cell charting
- It is restricted by the Pos and Time slicers
- For Key Figures, it uses VarianceAnalysis!D3 , which is populated by the XL3Link formulae on the KPI table within the Dashboard sheet.
- Contains an additional XL3Link in column E, contained within a grid calculation, to allow jump to detail on the selected product.
Time Series Chart Sheet
This contains a detail table and chart of value and target for the selected period, product, PoS, and the Key figure passed in by the xl3Linkformulae in Variance Analysis!$E$9: $E$29. Cell $O$8 is based on the selected month, and C:N use the XL3MemberNavigate() formula to choose the previous month.=, overall delivering a rolling 12 months.
Note that this could also be achieved in a grid, by driving the date hierarchy from $C$5 and choosing the previous 11
