topic Re: Problem with SET ANALYSIS and input field in QlikView

Problem with SET ANALYSIS and input field

Wed, 06 Jul 2011 12:53:45 GMT

Hello all,

I need help for a problem with the set analysis function. I have a table with customer number, a creation date of an invoice and a field with the value of the invoice.

For the users I like to create a input box, where there can input a date. Then I like to have a sum of the invoices from the customer where the creation date is less or equal as the date in the input box.

I have defined a variable in the document (FDatum) and create a input box for this variable. The database field called: OffenePostenHistorie.BELDATUM

The invove value field called: OffenePostenHistorie.OBETRAG

I have used the following set analysis function, but it doesn't work:

sum({$<OffenePostenHistorie.BELDATUM <= {$(FDatum)}>} OffenePostenHistorie.OBETRAG)

The format of the date in the table is YYYYMMDD and I put a date with the same format in the input box.

Can anybody give me a hint whats wrong?

Thanks in advance.

Michael

Problem with SET ANALYSIS and input field

Wed, 06 Jul 2011 13:14:48 GMT

Hi,

I believe it should be:

sum({$<OffenePostenHistorie.BELDATUM = { "<= {$(FDatum)}">} OffenePostenHistorie.OBETRAG)

Regards,

Jonathan

Problem with SET ANALYSIS and input field

Wed, 06 Jul 2011 13:39:04 GMT

Hi Jonathan,

thanks for your help, but it doesn't work ....

Re: Problem with SET ANALYSIS and input field

Wed, 06 Jul 2011 13:51:04 GMT

you have to make sure the date is interpreted as a number. So in you model you should calcuate

OffenePostenHistorie.BELDATUM as number same as for vaiable below.

Create a variable and evalute it as:

vDatum = num(Date#($(FDatum),'YYYYMMDD'))

Then use vDatum in set analyse

Regards,

Jonathan

Just tried it myself again:

Expr :

count({$< TetsDateValue= {">$(vDate)"}>} REQD_Number)

where TetsDateValue is calculated in model as round(Date('f'ieldwithdate') and vDate =

vDatum = num(Date#($(FDatum),'YYYYMMDD'))

Worked for me at least

Problem with SET ANALYSIS and input field

Wed, 06 Jul 2011 13:58:28 GMT

Hi Try the following

sum({$<OffenePostenHistorie.BELDATUM = {'<=$(FDatum)'}>} OffenePostenHistorie.OBETRAG)

regards

Problem with SET ANALYSIS and input field

Wed, 06 Jul 2011 14:40:07 GMT

Hi Nick,

that was the sollution for me! Your set analysis definition works great...

Many, many thanks to you!

Michael