topic dual value in set expression in QlikView

dual value in set expression

Sun, 13 Jun 2010 04:49:22 GMT

I have to use a fiscal year starting April 1st.

I have created dual values for year and fiscal year representation (FY 10-11, 2010) and I evaluate my fiscal year from the dates using year(addmonths(mydate, -3)).

This works as expected until it comes to set expressions.

I need to show the previous financial year value and tried

sum({<FIS_YEAR = { $(FIS_YEAR-1) }>} Sales)

FIS_YEAR - 1 returns me the numeric value (2009) while the FIS_YEAR in the set expression looks to be 'FY 09-10' because I can see that year total using sum({<FIS_YEAR = { 'FY 09-10' }>} Sales)

I haven't found a function that will give me the string value for the calculated fiscal year back. text() just returns the year as string but not the dual text value.

Thanks

Juerg

dual value in set expression

Mon, 14 Jun 2010 05:58:19 GMT

Hello Juerg,

If I understand your problem correctly, you have a natural year (4 digit integer) and a derived, formatted string (FY 10-11) representing your fiscal year. If you are going to use set operations on the fiscal year, you will likely save yourself a lot of extra coding by simply creating a third field: the fiscal year as a four-digit integer. Then use the existing formatted string while presenting the fiscal year data, but use the handy integer number when performing SET operations. I hope my suggestion is useful.

Best regards,

James

dual value in set expression

Thu, 17 Jun 2010 14:40:04 GMT

Hi James

Thought of this myself but it did not work as expected. I might have a data issue with this.

Thanks

Juerg

dual value in set expression

Thu, 17 Jun 2010 14:47:33 GMT

May be this post will help:

http://qliktips.blogspot.com/2010/01/more-on-dual-problem-in-sets.html