topic Re: Issues using variables/functions in set analysis in QlikView

Issues using variables/functions in set analysis

CM_B — Sat, 16 Nov 2024 03:54:00 GMT

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)

Re: Issues using variables/functions in set analysis

sunny_talwar — Wed, 10 Apr 2019 11:40:26 GMT

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

Re: Issues using variables/functions in set analysis

CM_B — Thu, 11 Apr 2019 12:30:36 GMT

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.

Re: Issues using variables/functions in set analysis

sunny_talwar — Thu, 11 Apr 2019 11:24:45 GMT

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

Re: Issues using variables/functions in set analysis

CM_B — Thu, 11 Apr 2019 12:15:38 GMT

Should be there now - apologies!

Re: Issues using variables/functions in set analysis

marcus_sommer — Thu, 11 Apr 2019 12:41:08 GMT

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

Re: Issues using variables/functions in set analysis

CM_B — Mon, 15 Apr 2019 08:59:06 GMT

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?

Re: Issues using variables/functions in set analysis

marcus_sommer — Mon, 15 Apr 2019 09:40:15 GMT

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