7 Replies Latest reply: Sep 11, 2018 2:20 AM by daisy ch RSS

    error in below set expression?

    daisy ch

      Hi

       

      I am writing the below set expression.

       

      Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>},[Action Priority]=1 distinct [Issue ID)

       

      before add the [Action Priority]=1 the expression is correct. When add the [Action Priority]=1 expression showing error.

       

      is there any syntax error. Please correct me.

       

       

      Thanks

        • Re: error in below set expression?
          arul settu

          maybe like this

           

          Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>},[Action Priority]={1} >}distinct [Issue ID])

          • Re: error in below set expression?
            Petter Skjolden

            It should be this:

             

            Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>},[Action Priority]={1 }>} distinct [Issue ID] )



            Red should be removed and green should be added .... so you end up with this:



            Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'},[Action Priority]={1}>} DISTINCT [Issue ID] )


              • Re: error in below set expression?
                daisy ch

                Thanks Petter,got it.

                • Re: error in below set expression?
                  daisy ch

                  Hi Peter need one more help in below expression.

                   

                  Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>} distinct [L1 Control ID]&' '&[Risk ID])

                   

                  in this above expression I want show only top 5 values. And above expression I am concatenate l1contolid and risk ID is it write that.

                   

                  my reqirement is caliculate top 5counts based on l1comtrolID and RiskID.

                   

                  thanks for your help.

                    • Re: error in below set expression?
                      Petter Skjolden

                      In general you can do a rank selection like this:

                       

                      If( Rank( Count( Sales ) ) <=5 , Count(Sales) )

                       

                      So in your case it should be:

                       

                      If(

                      Rank( Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>} distinct [L1 Control ID]&' '&[Risk ID]) ) <=5 ,

                      Count({<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'}>} distinct [L1 Control ID]&' '&[Risk ID])

                      )


                      Also be aware that top 5 could be more than 5 since ranking could be shared if the have the same value for the count.


                      Using a second parameter with Rank() would allow the expression to still work and select correctly more than 5 but up to ranking number 5:


                      Rank( ......  , 1 ) <=5


                      Look at the documention on Rank() to see the options you have...

                      https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/RankingFunctions/rank.htm

                        • Re: error in below set expression?
                          daisy ch

                          Hi Petter I need your help for below requirement.

                           

                          I have creating one bar chart dimension is region and expression count(RiskID).

                           

                          MY Table:

                          date

                          risk ID

                          status

                          region.

                           

                          above table date filed using that date field I am creating previous date and current date.

                           

                          I have only one excel data and only one date field.in that date field user select any two dates and compare those two dates one is previous date and other one is current date .what are the risk ID not available in previous date and available in current date than count the Risk ID.

                           

                          Ex:

                          01-jan-2018

                          16-feb-2018

                          02-mar-2018

                          10-apr-2018

                          12-jun-2018 like that.

                           

                          if user select 16-feb-2018 and 12-jun -2018 compare those two dates it means

                          16-feb-2018 date Risk ID not available and 12-jun-2018 Risk ID available count the that Risk IDs.

                           

                          Thanks

                    • Re: error in below set expression?
                      Ahmar Ansari

                      try this


                      Count(distinct {<[L1 Control Effectiveness]={'Needs Improvement'},[Risk Overall Risk Rating (R)]={'High'},[Action Priority]={'1'}>} [Issue ID])