Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

zagzebski
Contributor

Set analysis - comparing two field

Struggling a bit on some set analysis/expressions...

Data Structure:

ClientYearMonthRenewal Month
Rate
ABC2012010490
ABC2012020495
ABC20120304100
ABC20120404125
ABC20120504130
ABC20120604130
ABC20120704130
ABC20120804130
ABC20120904130
ABC20121004130
ABC20121104130
ABC20121204130

I need an expression (and the proper dimensions) that would compare the rate of the renewal month to the rate just prior to the renwal month and give the difference. (I would need to take the last two characters of the "YearMonth" field and compare to the "Renwal Month" field. I would be selecting a specific year so that would not be an issue of having multiple years. So the outcome of my formula for just the one client above would be (this would need to be a table that had all clients by their specific renewal month and the difference from the the rate just prior to their renwal month):

Client     Renewal Month     Rate Difference

ABC      04                         25 (125-100)

6 Replies

Re: Set analysis - comparing two field

See attached example


talk is cheap, supply exceeds demand
Not applicable

Re: Set analysis - comparing two field

Alternately, you could use rate - above(rate) and not bother with the set expressions at all.

zagzebski
Contributor

Re: Set analysis - comparing two field

Unfortunately I have to compare the "YearMonth" and "Renwal Month" from above. I don't have a flag like in your expression. Is there a way to write this into an expression?

Thanks

Re: Set analysis - comparing two field

You have to create those fields. You're loading the data from somewhere anyway, so simply add the two additional fields in that load statement. Just replace the inline load from my example with your load and add the two if(... lines. Just don't forget to use the correct field names or you'll get Unknown Field errors.


talk is cheap, supply exceeds demand
zagzebski
Contributor

Re: Set analysis - comparing two field

Got it, thanks.

A question on best practices of QlikView The only question I have is should a field be created in the script just to assist with one expression. This would probably be the only place it is used in any of our UI's.

Not applicable

Re: Set analysis - comparing two field

Try using If() function

Community Browser