topic Re: Expressions in text boxes in QlikView

Expressions in text boxes

Wed, 06 Aug 2014 11:53:08 GMT

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.

Re: Expressions in text boxes

simenkg — Wed, 06 Aug 2014 12:00:43 GMT

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

Try it and see if it works.

Re: Expressions in text boxes

Thu, 07 Aug 2014 06:38:29 GMT

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

Re: Expressions in text boxes

simenkg — Thu, 07 Aug 2014 08:37:19 GMT

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))

Re: Expressions in text boxes

Thu, 07 Aug 2014 09:06:56 GMT

That works!! Thank you very much!

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

Thanks

Re: Expressions in text boxes

simenkg — Thu, 07 Aug 2014 09:28:19 GMT

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.

Re: Expressions in text boxes

Wed, 24 Sep 2014 14:03:40 GMT

Of course! Thank you very much Simen.

Regards,

fcrysss