Thank you to the SQLBits committee, speakers, helpers and attendees, for making SQLBits 9 one of the best yet. What a great example SQLBits is of the power of the SQL Server community coming together to help each other, and enjoy a few beers in the process!
I was delighted to have my “PowerPivot & QlikView 101” session chosen by the committee, which I presented on Saturday afternoon. Thank you to all those who attended, great to see such a good number there. If you haven’t yet submitted your feedback for my session, and the event as a whole, please do take the time to do it. It helps everyone improve the quality of the event for next time.
For those who atttended my session, here are the slides, with a summary of the results and code etc.
Slides: Download the PowerPivot & QlikView 101 slide deck here
QlikView Script: Download the QlikView Script here
PowerPivot Workboook: Download the PowerPivot workbook here
Session Video: The session recording isn’t yet available, I’ll post a link here when it’s ready.
QlikView Expressions
Sales Amount Visual Cue for the green shading
Upper: (SUM(TOTAL SalesAmount) / COUNT(TOTAL SalesTerritoryRegion))*1.2
Lower: (SUM(TOTAL SalesAmount) / COUNT(TOTAL SalesTerritoryRegion))*0.8
Sales Amount blue bar Guage maximum
max(ALL Aggr(SUM(SalesAmount),OrderMonth))
Sales Amount Year to Date
rangesum(above(sum(SalesAmount),0,12))
Sales Amount Previous Year
SUM({<OrderYear={‘$(=Max(OrderYear)-1)’}>} SalesAmount)
PowerPivot DAX Expressions
Sales Amount Year to Date
=TOTALYTD(SUM(FactInternetSales[SalesAmount]), DimOrderDate[FullDateAlternateKey], ALL(DimOrderDate))
Sales Amount Previous Year
=CALCULATE(SUM(FactInternetSales[SalesAmount]), SAMEPERIODLASTYEAR(DimOrderDate[FullDateAlternateKey]), ALL(DimOrderDate))
PowerPivot Results
QlikView Results
If you have any questions about any of this, please get in touch.
Frog-Blog-Out