Community,
I am building a dashboard within Qlik sense that compares data from current period to a similar period one year earlier.
We have 2 different ways we view data: Calendar year and Performance year.
Calendar year is 1/1/xxx to 12/13/xxx
Performance year is 11/1/xxx to 10/31/yyy ( xxx can be 2017 and yyy can be 2018)
I pull the spend data monthly and totals for each subsequent month is a cumulative total since the beginning of the period e.g calendar 1/1/xx or Performance 11/1/xxx.
To access the data, I use a filter to select a period e.g 11/1/2017 to 12/31/2017.
For the current period, the filter returns the correct spend but when it comes to the previous year data:
Performance year Last year: 11/1/2016 to 12/31/2016, (same period), I have to keep tinkering with each field to manually change the dates in the formula.
Is there a way I can write this formula so that the correct spend from the previous year can show correctly by referring to the date range filter that I choose? I would like to avoid changing each date field for previous year data each month.
Formulas: Current period Spend = sum([Invoice Amount USD])
Performance year Spend: Last year = sum({<[Invoice Date]={'>11/01/2016'}>*<[Invoice Date]={'<12/31/2016'}>}[Invoice Amount USD]) (looking to get rid of these dates here by use of a formula that will utilize the filter for a period I chose)
Performance year period is 11/1/2016 - 10/31/2017 ( Nov - Oct)
Finally for my month Spend
= sum({<[Invoice Date]={">=$(=monthstart(addmonths(today(),-1)))<$(=monthstart(today()))"}>}[Invoice Amount USD])
I would like to modify this formula so that spend only shows the last full month data and ignores any spend in the current month. For example, since we are still in January, the data will only show December data until February 1st then switch to show January data. The above formula pulls in January spend as well which is not what I would like to show.
I hope my explanation is clear.
