Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mharm
Contributor

changing field value unintentionally

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.

5 Replies
MVP
MVP

Re: changing field value unintentionally

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.

mharm
Contributor

Re: changing field value unintentionally

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..  2017-03-10_7-27-20_unintentionalchange.png

karthiksrqv
Contributor II

Re: changing field value unintentionally

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.

mharm
Contributor

Re: changing field value unintentionally

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.

karthiksrqv
Contributor II

Re: changing field value unintentionally

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'