Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ZoeM
Specialist
Specialist

Script if/And expression

Hi Community.

I am building a Key Performance Indicator in a text box and I want to set some parameters on the value shown (%).

If no Year is selected in the listbox available, I want the KPI to show this years current status, if a year is selected I want the KPI to be the selected years status.

This is my expression:

=If(IsNull(GetFieldSelections(Year)),( ((Sum([FDJ Final S/O])/Sum([FDJ Final NTEI]))*Sum([FDJ Final NTEI]))/sum([FDJ Final NTEI]) and Year=Year(Today())) )

 

But I am getting a zero value. Without setting the Year, I am getting an average of all years. 

Any help rendered will be valuable. 

Thanks!

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

May be try this

=If(GetSelectedCount(Year) = 0,
 (
  (
   (
    Sum({<Year = {"$(=Year(Today()))"}>} [FDJ Final S/O])/
    Sum({<Year = {"$(=Year(Today()))"}>} [FDJ Final NTEI])
   )
   *
   Sum({<Year = {"$(=Year(Today()))"}>} [FDJ Final NTEI])
  )
  /
  Sum({<Year = {"$(=Year(Today()))"}>} [FDJ Final NTEI])
 ),
( ( ( Sum([FDJ Final S/O])/ Sum([FDJ Final NTEI]) ) * Sum([FDJ Final NTEI]) ) / Sum([FDJ Final NTEI]) )
)

View solution in original post

2 Replies
sunny_talwar

May be try this

=If(GetSelectedCount(Year) = 0,
 (
  (
   (
    Sum({<Year = {"$(=Year(Today()))"}>} [FDJ Final S/O])/
    Sum({<Year = {"$(=Year(Today()))"}>} [FDJ Final NTEI])
   )
   *
   Sum({<Year = {"$(=Year(Today()))"}>} [FDJ Final NTEI])
  )
  /
  Sum({<Year = {"$(=Year(Today()))"}>} [FDJ Final NTEI])
 ),
( ( ( Sum([FDJ Final S/O])/ Sum([FDJ Final NTEI]) ) * Sum([FDJ Final NTEI]) ) / Sum([FDJ Final NTEI]) )
)
ZoeM
Specialist
Specialist
Author

Thanks Sunny. That worked as a charm. 

I appreciate the speedy reply.