topic Re: Using Set Analysis with Date Variable in QlikView

Using Set Analysis with Date Variable

Wed, 26 Mar 2014 02:57:21 GMT

I have a simple problem and can't see why this is not working. I need to display values in table columns based on when payment is expected. I ma using the following Set Analysis:

sum({$<ARPaymentMonth={'=$(=Date(vEnd3MonthsBack,dd/mm/yyyy))'}>} ARInvoiceOutstandingAmount)

What is happening is ALL values are being returned, rather than just ones for the selected month. Any ideas?

Re: Using Set Analysis with Date Variable

sushil353 — Wed, 26 Mar 2014 04:23:19 GMT

Hi,

Try this one:

sum({$<ARPaymentMonth={'=$(=Date('$(vEnd3MonthsBack)',dd/mm/yyyy))'}>} ARInvoiceOutstandingAmount)

HTH

Sushil

Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 04:37:20 GMT

try this

sum({$<ARPaymentMonth={'=$(=Date('=$(vEnd3MonthsBack)',dd/mm/yyyy))'}>} ARInvoiceOutstandingAmount)

Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 04:56:32 GMT

Thanks for your response, however there appears to be a problem with this syntax:

Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 05:01:26 GMT

Try this

Create a variable for the expression

vARPMonth = Month(Date(vEnd3MonthsBack,dd/mm/yyyy))

then

SUM({<ARPaymentMonth={$(vARPMonth)}>}ARInvoiceOutstandingAmount)

sum({$<ARPaymentMonth={'=$(=Month(Date(vEnd3MonthsBack,dd/mm/yyyy)))'}>} ARInvoiceOutstandingAmount)

Re: Using Set Analysis with Date Variable

jagan — Wed, 26 Mar 2014 05:28:21 GMT

Hi,

Try like this, Outer and Inner are single quotes, because of this you are getting the error

sum({$<ARPaymentMonth={"=$(=Date('$(vEnd3MonthsBack)',dd/mm/yyyy))"}>} ARInvoiceOutstandingAmount)

Regards,

Jagan.

Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 06:28:21 GMT

I have tried this syntax as suggested … but still get the full dataset of values returned.

Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 06:32:33 GMT

I tried both options as suggested, but with this I get a zero value back. Here is the exact syntax I tried:

In the script:

let vEnd3MonthsBack = Date(monthend(addmonths(date(today()),-3)),dd/mm/yyyy);

In the chart table:

SUM({<ARPaymentMonth={$(vEnd3MonthsBack)>}ARInvoiceOutstandingAmount)

Re: Using Set Analysis with Date Variable

PrashantSangle — Wed, 26 Mar 2014 06:36:53 GMT

Hi,

I think in your table

ARPaymentMonth=contain Month

and expression you are using contain

Date('$(vEnd3MonthsBack)',dd/mm/yyyy) Which return Date

So that you have to use Month() or Monthname() before above expression to match with your fieldname

Try this

If ARPaymentMonth contain only jan,feb, etc than use,

sum({$<ARPaymentMonth={'=$(=Month(Date(vEnd3MonthsBack)))'}>}

sum({$<ARPaymentMonth={'=$(=Month(Date(vEnd3MonthsBack,'DD/MM/YYYY')))'}>}

IF ARPaymentMonth contain only jan 2013,feb 2013, etc than use

sum({$<ARPaymentMonth={'=$(=MonthName(Date(vEnd3MonthsBack)))'}>}

sum({$<ARPaymentMonth={'=$(=MonthName(Date(vEnd3MonthsBack,'DD/MM/YYYY')))'}>}

Regards,

Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 07:22:21 GMT

I have rechecked the data load – this is how it is formatted (I added the date function to see if it made a difference):

date(monthend(paymentDate),'dd/mm/yyyy') as ARPaymentMonth,

The script includes:

let vEnd3MonthsBack = Date(monthend(addmonths(date(today()),-3)),dd/mm/yyyy);

The table format is:

SUM({<ARPaymentMonth={$(vEnd3MonthsBack)>}ARInvoiceOutstandingAmount)

… and as mentioned, this loads ALL data values …

Re: Using Set Analysis with Date Variable

PrashantSangle — Wed, 26 Mar 2014 07:25:03 GMT

Hi,

In Your Date()

You use format 'dd/mm/yyyy'

in this mm---->minutes

not month

So use Format like 'DD/MM/YYYY'

MM--->Month

Regards

Re: Using Set Analysis with Date Variable

jagan — Wed, 26 Mar 2014 07:43:16 GMT

Hi,

Check whether the ARPaymentMonth and vEnd3MonthsBack is in this format "dd/mm/yyyy" if not convert to the same format. If possible attach a sample file.

Regards,

Jagan.

Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 08:13:57 GMT

Hi Jagan

I have rechecked all formats are correct. To recap:

Script now includes:

let vEnd3MonthsBack = Date(monthend(addmonths(date(today()),-3)),DD/MM/YYYY);

Data Load now includes:

date(monthend(paymentDate),'DD/MM/YYYY') as ARPaymentMonth,

The table format is:

sum({$<ARPaymentMonth={"=$(=Date('$(vEnd3MonthsBack)',DD/MM/YYYY))"}>} ARInvoiceOutstandingAmount)

… this returns ALL data to the table – not just the required items.

Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 08:15:09 GMT

I have rechecked this with all date formats as DD/MM/YYYY … same result …

Re: Using Set Analysis with Date Variable

PrashantSangle — Wed, 26 Mar 2014 08:25:51 GMT

Hi,

I am just go through all your expression you are not using ''(Single Quotes) to format your date,

Try this If you are using variable

sum({$<ARPaymentMonth={"=$(=Date('$(vEnd3MonthsBack)','DD/MM/YYYY'))"}>} ARInvoiceOutstandingAmount)

and also check for Other expressions also .

Regards,

Re: Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 09:15:20 GMT

Hi,

Use this to create variable

let vEnd3MonthsBack = monthend(addmonths(date(today(),'dd/mm/yyyy'),-3));

Expression:

SUM({<ARPaymentMonth={'$(vEnd3MonthsBack)'}>}ARInvoiceOutstandingAmount)

It will work...

Re: Using Set Analysis with Date Variable

Wed, 26 Mar 2014 13:43:40 GMT

Thanks for your help, everyone The correct solution was:

In the script:

let vEnd3MonthsBack = monthend(addmonths(date(today(),'dd/mm/yyyy'),-3));

In the chart table:

SUM({<ARPaymentMonth={'$(vEnd4MonthsBack)'}>}ARInvoiceOutstandingAmount)

(Ravi - your solution had a '}' missing, but I resolved that)