Set analysis - using 4 variables to compare date ranges 1 year apart

Thu, 06 Nov 2014 13:41:25 GMT

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.

Re: Set analysis - using 4 variables to compare date ranges 1 year apart

tim_at_ford — Wed, 22 Jan 2020 20:02:26 GMT

I also am looking at this type of problem. Is there anyone out there that has done this???

topic Re: Set analysis - using 4 variables to compare date ranges 1 year apart in QlikView

Set analysis - using 4 variables to compare date ranges 1 year apart

Re: Set analysis - using 4 variables to compare date ranges 1 year apart