Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following script in the data load
If(Month(MonthYear) >= 10, Month(MonthYear)-9, Month(MonthYear)+3) as FYYearMonthNum,
If(Month(MonthYear) >= 10, Text(MonthName(addYears(MonthYear, 1))), Text(MonthYear)) as FYDisplayYearMonth,
If(Month(MonthYear) >= 10, YearName(addyears(FYYear, 1)), FYYear) as FYDisplayYear
MonthYear is the actual calendar year value. I do not want it to change. however, I want to create a fiscal year string, so I'm using that value to create the display value..
When MonthYear is Oct 2017, create an FYDisplayYear month to be a string of Oct 2018. however, the problem with how I'm doing it above is that it's also changing the MonthYear field value to Oct 2018 as well. how can I reference MonthYear in a function without changing its value in the process? same thing for the FYYear. I have the FYYear as 2017, but I want create another field value called FYDisplayYear and make it say 2018. however, when I reference the YearName(addyears(FYYear, 1)) function, it's also changing the FYYear value from 2017 to 2018.
The strange behavior you observe is not due to the fact that the functions change fields unintentionally. It is due to the values being next to each other in the same table row. So when you do selections in the application on one field the other fields and the value associated (on the same row) will become "white" as it is associated.
It is exactly how the association logic is supposed to work in Qlik.
It is actually changing my MonthYear field
here is the entire load script that I used.
If(Month(MonthYear) >= 10, Month(MonthYear)-9, Month(MonthYear)+3) as FYYearMonthNum,
If(Month(MonthYear) >= 10, 'Increment by 1', Text(MonthYear)) as FYDisplayYearMonth,
If(Month(MonthYear) >= 10, 'increment by 1', FYYear) as FYDisplayYear
//If(Month(MonthYear) >= 10, Text(MonthName(addYears(MonthYear, 1))), Text(MonthYear)) as FYDisplayYearMonth,
//If(Month(MonthYear) >= 10, YearName(addyears(FYYear, 1)), FYYear) as FYDisplayYear
value..
Hi Maria,
If your MonthYear is say being stored as 122017, then I don't think this is correct "addYears(MonthYear, 1)". It is just going to giving you a date that is 1 year away. The addYears accepts a date format as input.
but why would my MonthYear field value change to "increment by 1' when I never changed MonthYear to 'increment by 1'. I changed ONLY FYDisplayYear and FYDisplayYearMonth to increment by 1...at least that's what I think my script is doing..obviously I'm wrong.
Yep, my best guess is somewhere in your script, you might be renaming your field to the MonthYear field. or you are reading in your expression twice, once with the ' expression as MonthYear' and the 2nd time with 'expression as FYDisplayYearMonth'