topic Re: Set analysis - comparing two field in QlikView

Set analysis - comparing two field

zagzebski — Thu, 18 Apr 2013 16:37:17 GMT

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

Data Structure:

Client	YearMonth	Renewal Month	Rate
ABC	201201	04	90
ABC	201202	04	95
ABC	201203	04	100
ABC	201204	04	125
ABC	201205	04	130
ABC	201206	04	130
ABC	201207	04	130
ABC	201208	04	130
ABC	201209	04	130
ABC	201210	04	130
ABC	201211	04	130
ABC	201212	04	130

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)

Re: Set analysis - comparing two field

Gysbert_Wassenaar — Thu, 18 Apr 2013 16:57:28 GMT

See attached example

Re: Set analysis - comparing two field

Thu, 18 Apr 2013 17:01:49 GMT

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

Re: Set analysis - comparing two field

zagzebski — Thu, 18 Apr 2013 17:17:39 GMT

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

Gysbert_Wassenaar — Thu, 18 Apr 2013 17:24:55 GMT

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.

Re: Set analysis - comparing two field

zagzebski — Thu, 18 Apr 2013 17:51:40 GMT

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.

Re: Set analysis - comparing two field

Thu, 11 Sep 2014 17:56:17 GMT

Try using If() function