Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

thonipad
Contributor III

Set Analysis Compare two columns

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Set Analysis Compare two columns

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.

5 Replies
Highlighted
MVP
MVP

Set Analysis Compare two columns

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
Honored Contributor

Set Analysis Compare two columns

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.

thonipad
Contributor III

Set Analysis Compare two columns

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.

thonipad
Contributor III

Set Analysis Compare two columns

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.

Not applicable

Re: Set Analysis Compare two columns

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