Exercises
I thought I might as well showcase some of the more exciting exercises that I have done. This is preparation for the certifications. OBS this is only to learn, these are no complete objects.
AVG Sales For Specific Products
Show average sales per product, but only for products that have at least 2 sales entries
Measure:
Avg(Aggr(If(Count(Sales) >= 2, Avg(Sales)),Product))
Customer With More Than 3 Orders
We checks if the order count per customer is greater than 3. Then count how many customers meet that rule. Lastly divides by total distinct customers.
Measure:
Num(Count(DISTINCT Aggr(If(Count(OrderID) > 3, CustomerID),CustomerID)) / Count(DISTINCT CustomerID),'#.##%')
Coloring Through Expression
This highlights only the region with lowest sales in red, others in gray
Color by Expression:
If(Sum(Sales) = Min(TOTAL Aggr(Sum(Sales), Region)),'red','gray')
Alternative States
This exercise was a fail but I think that the alternative state function shows promise. There are no hard expressions in this exercise, however there are master item states to assign and take into account. I prefere variable or container...
Exclude Selected Products from KPI
The first KPI shows the selected product's sales and in the second we exclude sales of each product when filtering.
Measure:
Sum({1<Product-=P(Product)>} Sales)
Level 3
Dynamic Commentary Text Object
This concatenates the text with the selected Month and Sales.
Measure:
='Sales in ' & GetFieldSelections(Month) & ': ' & Num(Sum(Sales), '#,##0')
AVG Sales For Specific Products
Show average sales per product, but only for products that have at least 2 sales entries
Measure:
Avg(Aggr(If(Count(Sales) >= 2, Avg(Sales)),Product))
Customer With More Than 3 Orders
We checks if the order count per customer is greater than 3. Then count how many customers meet that rule. Lastly divides by total distinct customers.
Measure:
Num(Count(DISTINCT Aggr(If(Count(OrderID) > 3, CustomerID),CustomerID)) / Count(DISTINCT CustomerID),'#.##%')
Coloring Through Expression
This highlights only the region with lowest sales in red, others in gray
Color by Expression:
If(Sum(Sales) = Min(TOTAL Aggr(Sum(Sales), Region)),'red','gray')
Pivot Table with YoY Growth %
First year will be blank (no prior year to compare). I like the pivot table over the straight one as this is more effective. Found that we had to use Before instead of Above...
Measure:
Num((Sum(Sales) - Before(Sum(Sales))) / Before(Sum(Sales)),'#.##%')
Exclude Selected Products from KPI
The first KPI shows the selected product's sales and in the second we exclude sales of each product when filtering.
Measure:
Sum({1<Product-=P(Product)>} Sales)
Level 2
Combo Chart with Variable
Toggle between monthly and quarterly view using a field called PeriodLevel. This was learnt in order to use a variable to change dimension.
Variable:
Pick(Match(GetFieldSelections(PeriodLevel, ',', 1), 'Month', 'Quarter'), 'Month', 'Quarter' )
Dynamic KPI
Very easy but effective KPI. If the user selects one year, show sales for that year. If the user selects no year, default to latest year.
Measure:
If(GetSelectedCount(Year)=1, Sum(Sales), Sum({<Year={"$(=Max(Year))"}>} Sales))
Dynamic KPI
This will show % of total sales from Premium customers
Measure:
Num(Sum({<CustomerType={'Premium'}>} Sales) / Sum(TOTAL Sales),'#.##%')
Dynamic Dimension
If one year is selected, then we show Month. If multiple or no years selected, then we show Years.
Dimension:
If(GetSelectedCount(Year) = 1, MonthName, Year)
Cumulative Sales
Again, simple but effective.
Measure:
RangeSum(Above(Sum(Sales), 0, RowNo()))
Level 1
Kontakta mig
Email: max@clickcpace.se
Phone: +46(0)793 37 82 82