1. What if two years are selected? Is your application restricting users from making multiple selections in the "Year" filter box?
2. Do you have a separate field for CAN$ sales? Or you are using a conversion rate to display Sales in different currency?
3. What do you mean by "switching between Net Sales and Gross Sales" ? Do you need to display different values in the same column? How these sales values are organized in your data model?
1. We display our sales dashboard in periods of two years, and show the growth compared to previous year. So, for this solution the users will only be able to select one year at a time, and see two years of data at a time, Default year will be current year (2016) and last year, and if user selects 2015, then 2014 is last year's data.
2. Yes, Canada sales in CAD has its own field, the conversion happens in the data source, which then gets loaded into Qlik in CAD already.
3. Net Sales and Gross sales are different fields as well. Only one value, at this time, will be displayed. So users can choose between Net or Gross, but Gross is the default value. We could organized these two fields to be displayed side by side, but users want to see them individually.
Another thread will help with the Currency: Dynamically changing number formats?
As far as Year & Previous Year - I normally use a MAX([YearField]) to return the selected year (takes care of multiple selections) and store the results into variables (previous year obviously being MAX([YearField])-1)
Net & Gross could be similar - put an IF statement into the displayed measure, base on a variable setting
e.g. SUM(IF(vSales = 'Net',[Net Sales],[Gross Sales]))
Those are great ideas and using the MAX year if the best option. How would you combine all these variables together?
For every chart and table, users have to be able to switch between cases and dollars, Years, Net sales and Gross sales, and US and Canada.
My biggest issue is finding the best way to apply all these variables together.
I also found this post below which gave me good ideas as well.
SET vCurrency = IF($1 = 'CAD',NUM($2,'£#,##0.00'),NUM($2,'$#,##0.00'));
then call it as:
=vCurrency('CAD',IF(vSales='Net',SUM([Net Sales]),SUM([Gross Sales])))
LOAD * INLINE [
then create a variable called vSales as:
Haven't tested any of this by the way!
apologies - put it in a quick test and need some changes!
MAX won't work on text, so create an inline as:
LOAD * INLINE [
then two variables:
vSalesTotal: =IF(vSales=1,SUM([Net Sales]),SUM([Gross Sales]))
then your measure becomes:
substituting 'CAD' for your Currency field
You can then create a FILTER pane with [Sales] as the Dimension - then when the user selects Net it changes to [Net Sales] otherwise its [Gross Sales]