topic This set analisys should work! in QlikView

This set analisys should work!

fernandotoledo — Thu, 20 Jan 2011 14:08:00 GMT

Hi all,

This example shows something strange, a simple set analisys statement using a date field that don´t work, and I think it should!

This is the expression:

Sum ({< PERIOD = {$(=max(PERIOD))} >} VALUE)

Any sugestions?

Best regards,

Fernando

This set analisys should work!

Thu, 20 Jan 2011 15:16:51 GMT

Try this:

Sum ({< PERIOD = {"=$(=max(PERIOD))"} >} VALUE)

This set analisys should work!

fernandotoledo — Thu, 20 Jan 2011 15:25:42 GMT

nope...I´ve tried this one, at least it don´t return zeroes..

This set analisys should work!

Thu, 20 Jan 2011 15:36:12 GMT

I've try that with your example and it returns values. What do you specifically expect to be returned?

This set analisys should work!

fernandotoledo — Thu, 20 Jan 2011 15:54:10 GMT

Thanks for your concern. It should bring "0" to all lines but the last, wich value is associated with the last selected month. Like the second column in the example. But to do that one, I brought the date field as number using num(X,'0') in the script.

The fact is that the expression should work with date the same way it work with numbers... with no further format manipulation...

This set analisys should work!

Thu, 20 Jan 2011 16:01:46 GMT

Your PERIODS are string values. Your PERIOD_NUMs are numbers. In your second expression, you can see it is returning 40878 for the element set. In Set Analysis, you have to be careful that the formats match exactly. You need it to return:

Sum ({< PERIOD = {'dez 2011'} >} VALUE)

I couldn't get that using the Date() function, but I was able to do it using Month(), Year() and the ascii code for a blank space. Try this:

Sum ({< PERIOD = {'$(=month(max(PERIOD)) & Chr(32) & Year(max(PERIOD)))'} >} VALUE)

This set analisys should work!

fernandotoledo — Thu, 20 Jan 2011 16:12:04 GMT

Hi Miller,

You are right!

But my point is that the PERIOD field is DUAL (string and number, such as month), so it should work either way. It looks like qlikview can only work with the numeric value of the fields when evaluating Set Analisys statements.

I should change the topic from question to discussion!

Regards

Fernando

This set analisys should work!

Thu, 20 Jan 2011 16:28:41 GMT

It doesn't look like you actually defined it as Dual in your load script. I don't think it matters though, even using Dual(), the Set Analysis doesn't seem to work. I wouldn't say QlikView can only work with numeric values, because my expression uses the string values.

I would say that Set Analysis does not work with the numeric piece of a dual field. I had never come across this issue before.

This set analisys should work!

fernandotoledo — Thu, 20 Jan 2011 16:49:00 GMT

Yes, its dual: dual MonthName (date[, period_no = 0])

And I agree that it don´t work with the numeric piece of a dual field.

But what´s funny is that if you create the field using monthname() you get a result, if you use DATE(monthstart(),'MMM YYYY') you get another one!

This set analisys should work!

Thu, 20 Jan 2011 16:52:49 GMT

Try

Sum

({<PERIOD={$(=GetFieldSelections(PERIOD))}>} VALUE)