topic Re: Using set analysis to get a set of dates based on variables in QlikView

Using set analysis to get a set of dates based on variables

Wed, 20 Feb 2013 09:37:54 GMT

I have a problem that I have struggled with for too long. The basic problem is that I want to get the sum of sales between two different dates, based on variables. When I write my expression using two static dates, it works. Below it the code with static dates:

Sum( { $ < Date= P ( { 1< OrderDateNum= { ">=38930<=38960" } > } Date) >} OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

As you can see I use the numeric value for dates. In my load script I have created an extra field "OrderDateNum" for matching. Maybe I have made things more complex, but I have tried and googled everything. It all comes down to converting I think. Below it the expression that I have tried:

Sum( {$ < Date= P ( { 1< OrderDateNum= { ">=$(_vMTD_start_date)<=$(_vChosenDate)" } > } Date ) >} OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

I have the two variables _vChosenDate and _vMTD_start_date, and they are booth the numeric versions of dates. Apparently the upper limit variable "_vChosenDate" is used, but not the lower limit variable.

I have also tried the following,

Sum( { $ < Date= { ">=DATE($(_vMTD_start_date_NUM))<=DATE($(_vChosenDate_NUM))" } > } OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

Can anyone help me?

Re: Using set analysis to get a set of dates based on variables

Gysbert_Wassenaar — Wed, 20 Feb 2013 09:46:12 GMT

You're probably right that the date format is the problem. Make sure your date fields and two variables contain real dates, not strings.

Check if one of these works:

Sum( { $ < Date= { ">=$(_vMTD_start_date)<=$(_vChosenDate)" } > OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

Sum( { $ < Date= { ">=$(=num($(_vMTD_start_date)))<=$(=num($(_vChosenDate)))" } > OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

Sum( { $ < OrderDateNum= { ">=$(_vMTD_start_date)<=$(_vChosenDate)" } > OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

Sum( { $ < OrderDateNum= { ">=$(=num($(_vMTD_start_date)))<=$(=num($(_vChosenDate)))" } > OrderQty * UnitPrice * ( 1 - UnitPriceDiscount ) )

Re: Using set analysis to get a set of dates based on variables

Wed, 20 Feb 2013 13:13:44 GMT

Thanks, but your suggestions didn't work. I'm putting this problem on hold for today, so that I can look at it with new perspective tomorrow.

What puzzles me is that when I user numbers in my expression (my first example) it works fine, and when I look at my variables in text-boxes they show the exact same numbers. But when I try to use the variables in the expression, it doesn't work. What I need is some nice way of looking at tables and varables and show the data types, to make sure that I have it right.