Hello experts - I hope you can help.
I have created a table which shows sales quantities for each salesperson within a date range which is set using 2 calendar controls (start and end dates for the range)
The date on the 'start' calendar sets variable vStartTranDate, and the date on the 'end' calendar sets variable vEndTranDate.
The expression for the sales quantity column (which works fine) is this:
= sum({<SourceOfSale={'CallCentre'}, DateOfTransaction= { "$(= '>=' & date(vTranStartDate,'YYYY-MM-DD') & '<=' & date(vTranEndDate,'YYYY-MM-DD'))" } >} SalesQuantity)
I want the next column to be the same, but for transaction date range 1 year back from the user's chosen start and end dates
First I setup two variables to derive the right range (vOneYearBeforeTranStartDate & vOneYearBeforeEndStartDate) which simply use AddYears to subtract 1 year from each of vStartTranDate & vEndTranDate
Then I added the new column with this expression:
= sum( { $ <SourceOfSale={'CallCentre'}, DateOfTransaction= { "$(= '>=' & date(vOneYearBeforeTranStartDate,'YYYY-MM-DD') & '<=' & date(vOneYearBeforeTranEndDate,'YYYY-MM-DD'))" } > } SalesQuantity)
This produced zeroes, this must be because within the current selection there is no data for the earlier date range. ie the user selects a range from December 17th 2013 to January 16th 2014, so data from 1 year before is excluded.
So then I tried replacing = sum( { $ <SourceOfSale={'CallCentre'} etc with = sum( { 1 <SourceOfSale={'CallCentre'} which kind of works but not really because it remains unaffected by further selections on other fields, which is not what I want
Then I tried -
= sum( { $ <DateOfTransaction=, SourceOfSale={'CallCentre'}, DateOfTransaction= { "$(= '>=' & date(vOneYearBeforeTranStartDate,'YYYY-MM-DD') & '<=' & date(vOneYearBeforeTranEndDate,'YYYY-MM-DD'))" } > } SalesQuantity)
- thinking it would ignore the user's date selection and instead respect only the vOneYearBefore... variables - just more zeroes
The only other suggestion I've seen that looks vaguely helpful is to use something like
Sum( { $ < Year = { $ (=Only(Year)-1 } > } SalesQuantity )
which would be fine until you get into ranges which span more than year (like in my December 17th 2013 to January 16th 2014 example)
There's a lot of reading out there on this, but it seems geared to the logic that would make that last theory work, and that doesn't help me with the scenario of free date selection rather than set years/quarters/months.
At this point any advice would be gratefully received. Thank you.
