Expressions in text boxes

[#1]

Hello Community,

I have a few text boxes on my module to show a few summary figures, such as Last Year Terminations, Last year New Starters, etc.

Can I use Set Analysis in text boxes or are there limitations? Because I don't seem to be able to get a summary to work when considering terminations from year before the selected one.

I tried:

=count(if(inyear(Termination_Date,vMaxDate,-1,7),HRP_Code))

but it does not work. This same expression will work when instead of Termination_Date I query Start_Date, but that's because start date is associated to my master date. That's why I thought Set Analysis would work, but I can't get the syntax right.

[simenkg]

=count({$<Termination_Date = P({1<Year={"$(=Year(vMaxDate)-1)"}>}Date)>}HRP_Code))

Try it and see if it works.

[#3]

Thank you Simen,

It does work for calendar year, but I need it to consider fiscal year...

I've tried to change it but I can't get it to work.

The script creates fields to calculate [Fiscal Year] etc.

Thanks

[simenkg · Accepted solution]

Create a [Fiscal Year Num] field in your script where 1 is the first fiscal year in your model and up to the latest one.

Then you should be able to use the following logic:

=count({$<Termination_Date = P({1<[Fiscal Year Num]={"$(=max([Fiscal Year Num])-1)"}>}Date)>}HRP_Code))

[#5]

That works!! Thank you very much!

Just out of interest, why can't I use [Fiscal Year] instead of [Fiscal Year Num] ?

Thanks 

[simenkg]

Year(Date) - 1 givers you the last year because 2014 - 1 = 2013

[Fiscal Year Num] - 1 gives you the last [Fiscal Year Num] because 5 - 1 = 4

[Fiscal Year] - 1 is not defined natively in QlikView (to my knowledge) because  '2013-2014'-1 makes no sense.

[#7]

Of course! Thank you very much Simen.

Regards,

fcrysss