Solved: This set analisys should work! - Qlik Community

fernandotoledo · ‎2011-01-20

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

Report Inappropriate Content · ‎2011-01-20

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)

View solution in original post

Report Inappropriate Content · ‎2011-01-20

Try this:

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

fernandotoledo · ‎2011-01-20

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

Report Inappropriate Content · ‎2011-01-20

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

fernandotoledo · ‎2011-01-20

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...

Report Inappropriate Content · ‎2011-01-20

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)

fernandotoledo · ‎2011-01-20

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

Report Inappropriate Content · ‎2011-01-20

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.

fernandotoledo · ‎2011-01-20

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!

Report Inappropriate Content · ‎2011-01-20

Try

Sum

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