Set Analysis Compare two columns

[Anonymous]

I have a text columns which has values 201001, 201002 etc. I have a different date column as well. How do I write a set analysis which goes something like this:

sum({<date(date_column,'yyyymm') = {text_column}>} Amount_Column)

The above is not working. What am I doing wrong?

Thanks,

Dinesh.

[Oleg_Troyansky · Accepted solution]

If these are two columns of a chart, they can't be compared like this in Set Analysis. The Set Analysis condition is being evaluated once for the whole chart, but not for each cell in the chart.

You'll need to find an alternative either using IF functions (very heavy on large data sets), or looking for an alternative data modeling solution that would allow linking the two fields and leveraging QlikView associative logic.

[pover]

You can not write Set Analysis that makes reference to values in a row, but an if() should work:

sum(if(date(date_column,'yyyymm') = text_column, Amount_Column))

You might also need to change the date to a text, too.

text(date(date_column,'yyyymm'))

Regards.

Edit: The same as Oleg says.

[Anonymous]

Thanks for the clarification. Fortunately for me, the data size / performance associated with an IF statement is not an issue. However, I am still not getting what I want. I think the root cause could be that date(date_column,'YYYYMM') is just changing the display, but not changing the actual value itself. What is the best function to extract the value of YYYYMM from a date without loosing the padded 0's when the month is from 0 thru 9?

Regards,

Dinesh.

[Anonymous]

I figured out the issue. The text column was really a numeric column. Once I converted into text, it worked out. Thank you both.

Regards,

Dinesh.

[#6]

So you could use Set syntax to compare two columns? Can you please paste your Set syntax (after you converted to text?)