Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
See attached example
Alternately, you could use rate - above(rate) and not bother with the set expressions at all.
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
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.
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.
Try using If() function