Issues using variables/functions in set analysis

[CM_B]

I am using  a pivot table to find the volume of transactions for the current selected period and the previous selected period.  Unfortunately my client wants to see both years/months/days as the selected date.

I am using the below formula for selected period

=sum({<CalendarDATE = {">=$(vMinCompareDate) <=$(vMaxCompareDate)"}>}COUNT_TRADE_VOL)

I am using the below formula for previous selected period (in this case previous month)

=SUM({<CalendarDATE={'>=(Date(vPrevMonthMax))<=$(=Date(vPrevMonthMin))'}>}COUNT_TRADE_VOL)

The result is not correct; Both columns are being returned with the same volume.  Is any one able to offer any advise please?

Variables:

min compare date =(AddYears(date(Min(CalendarDATE)),0))

Max compare date =(AddYears(date(Max(CalendarDATE)),0))

PrevMonthMin = =AddMonths((vMinCompareDate),-1)

PrevMonthMax = =AddMonths((vMaxCompareDate),-1)

[sunny_talwar]

Would you be able to share a sample to check this out?

[CM_B]

Thank you for your reply.  I have put together a sample- I am using client data so have modified- I am also using a database and have included the data in the simplified example script.  The Data and variables are there.

[sunny_talwar]

I don't see the variables in the script? Did you attach the correct file?

[CM_B]

Should be there now - apologies!

[marcus_sommer]

I assume that your date() creation within the script caused this behaviour and that you need to apply a converting before formatting it. This means doing something like the following:

Date(date#(CalendarDATE, 'YYYYMMDD')) as CalendarDATE,

- Marcus

[CM_B]

Thanks @marcus_sommer I have used this format in my data extract script.. would you also recommend using this format within all of the variables?

[marcus_sommer]

It's difficult to say. The conversion with date#() in the script is needed because otherwise your data are any number or even a string but no date and none of various date-functions/features would be applicable.

In general it's recommended to use pure numbers - for example created with num() or floor() - if dates need to be calculated and/or matched in any way because the handling with formatted dates could be quite tricky and will always depend from the context in which it should be used. This means the handling of formatted values will always require additionally efforts. Quite often it's useful to have all needed formattings and pure numbers within the master-calendar and then to use the appropriate and further helpful is to create there flags for things like YTD, MTD, LYTD and so on.

In regard to variables - they should simplify the things - and here I'm not sure if they do it in your case. I think I wouldn't use them for what you describe because it will end in nested variables - and probably a lot of them. Of course it will be possible but usually are the efforts for it much higher as the benefits especially if there are other possibilities like the mentioned flags or maybe the use of island-tables.

To get a bit more background take a look here:  How-to-use-Master-Calendar-and-Date-Values and here: Variables.

- Marcus