topic Re: Syntax set analysis in QlikView

Syntax set analysis

joris_lansdaal — Thu, 10 Nov 2016 15:41:26 GMT

Hi all,

I have a syntax issue. In my app I've got the field Target_FY_Name: 2016, FY16, FY15-16.

For Target_FY_Name = 2016:

The working syntax is: Sum({<Target_FY_Name={"$(=Year(Today()-2))"}>} Target_FY)

But I don't know how to write the syntax in combination with Text.

For Target_FY_Name = FY16:

Sum({<Target_FY_Name={"........$(=Right(Year(Today()-2),2)"}>} Target_FY)

I've Tried 'FY'& but can't find the right syntax..

Can anyone help me?

Re: Syntax set analysis

Oleg_Troyansky — Thu, 10 Nov 2016 16:16:26 GMT

Something like this:

Sum({<Target_FY_Name={$(='FY' & Right(Year(Today()-2),2)}>} Value)

No need in any quotes since you are new searching, just calculating a value. And, you are not summing up Fiscal Years, are you? If you wanted to see that FY value, use ONLY() with the same Set Analysis.

Cheers,

Oleg Troyansky

Learn Set Analysis and many other advanced Qlik techniques in my book QlikView Your Business.

Re: Syntax set analysis

joris_lansdaal — Fri, 11 Nov 2016 05:30:13 GMT

Thank you, Oleg!

Re: Syntax set analysis

joris_lansdaal — Fri, 11 Nov 2016 08:12:14 GMT

Hi Oleg,

When I expand the search to the below it returns Nulls =( F15-16)

=sum({<Target_FY_Name={$(='FY'&right(Year(Today())-1,2) & '-' & right(Year(Today()),2) )}>}Target_FY)

Without the part: & '-' & right(Year(Today()),2) it works fine.

Am i doing something wrong?

thanks

Re: Syntax set analysis

Oleg_Troyansky — Sat, 26 Nov 2016 23:40:47 GMT

Sorry for the delay, I've been swamped...

It's hard to troubleshoot without seeing your data. I'd recommend to add your formula as an expression in a Straight table and leave the label empty. You will be able to see how the $-sign expansion is resolved. It sounds that you have a fiscal year FY15 but not FY15-16...

Re: Syntax set analysis

joris_lansdaal — Wed, 30 Nov 2016 10:20:27 GMT

Hi Olek,

I previously used a minus sign and now i use an underscore. FY 15-16 vs FY 15_16. The latter works fine.

I've isolated the issue in how to handle the minus in the syntax, don't know how to handle (the minus) in the future.

sum({<Target_FY_Name={$(='FY'&right(Year(Today())-1,2) & '-' & right(Year(Today()),2) )}>}Target_FY)

Cheers