25 Replies Latest reply: Jun 22, 2017 12:16 PM by Sunny Talwar RSS

    How can I list values including text format with a condition on a chart?

    Ozgur Cirak

      Hello,

       

      I work as a analyst manager in a company. I came across a bottle neck with qlik sense.

      We have traffic systems installed in different countries and i have to deliver monthly reports about percentage of violations, enforcements etc.

      The systems are so called TVR s have their unique ID's. My database is excel sheets with a clear format.

      My main goal is to create a chart(line,bar etc.) with tower IDs as a dimension and lets say checked violations as a measure. But i want to set one or two conditions for TVR ID dimension.I couldnt find anywhere how to do it.You can find related app file attached. At the last page which called Monthly results i have a a chart and a invalid dimension. I'm new to script language and what i wrote was :


      if(count({<[Violation Profile]={'Green signal issue'}>}[Violation Profile])>1000,[TVR Code-Viol_March.TVR Code],)

      Can you help mi with that?

      Best regards

        • Re: How can I list values including text format with a condition on a chart?
          Sunny Talwar

          What is the output you are looking to get? The problem is that you cannot use Aggregate functions such as Count, Sum, Min, Max in the script without Aggr() function (or TOTAL qualifier). But I don't know what you are looking to get to suggest anything. Can you explain what you want and may be share the output you are looking to get?

          • Re: How can I list values including text format with a condition on a chart?
            Ozgur Cirak

            Hi Sunny ,

             

            I could not understand what was your point with aggr() obligation. I have so many apps that are running correctly on script without aggr(). I only used it with firstorderget function.

             

            So i have a database file like this. Checked means correct violations, the others are system issues. what i m trying to do is making a line or bar chart and adding TVR codes as a dimension. But i want to set conditions when defining this dimension.

             

            For example, is it possible to show only tower codes with more than 5 checked violation profile count and with more than %50 checked/total violation profile ratio?

             

              

             

            TVR CodeDateTimeDay CycleViolation Profile
            IP55CTVR201.03.201700:03NightImage problem
            IP47ATVR101.03.201700:06NightChecked
            IP72TVR201.03.201700:32NightChecked
            IP47ATVR101.03.201700:39NightImage problem
            IP55CTVR201.03.201700:55NightChecked
            IP47ATVR101.03.201701:11NightImage problem
            IP2TVR101.03.201701:20NightImage problem
            IP20TVR101.03.201701:38NightChecked
            IP20TVR101.03.201701:47NightImage problem
            IP2TVR101.03.201701:56NightChecked
            IP76TVR301.03.201702:23NightImage problem
            IP2TVR101.03.201702:39NightImage problem
            IP26TVR101.03.201702:59NightEmergency situation
            IP55CTVR201.03.201703:19NightImage problem
            IP1ETVR201.03.201704:03NightImage problem
            IP28ATVR101.03.201704:32NightImage problem
            IP55CTVR201.03.201704:34NightChecked
            IP55CTVR201.03.201705:03NightChecked
            IP20TVR101.03.201705:08NightChecked
            IP55CTVR201.03.201705:14NightChecked
            IP40TVR401.03.201705:43DayImage problem
            IP76TVR201.03.201705:48DayChecked
            IP55CTVR201.03.201705:50DayChecked
            IP20TVR101.03.201706:11DayChecked
            IP55CTVR201.03.201706:16DayChecked
            IP2TVR301.03.201706:31DayYellow light
            IP55CTVR201.03.201706:37DayChecked
            IP55CTVR201.03.201706:39DayChecked
            IP40TVR401.03.201706:47DayChecked
            • Re: How can I list values including text format with a condition on a chart?
              Ozgur Cirak

              Hi Sunny,

              I could not understand what was your point when saying i have to use aggr() function in a script. I have so many apps at the attachment that are working fine without it. Just for firstordervalue(), i use aggr() function

               

              To give an example: This table represents my database. Checked means correct and the others are system problems.

               

              I want to make a bar or line chart and add TVR Codes s as dimension. But i  dont want my chart to show all codes. I want to set one or two conditions.

               

              For example; i just want to see TVR Codes with more than 10 checked violation profile count.

               

                   

              TVR CodeDateTimeDay CycleViolation Profile
              IP55CTVR201.03.201700:03NightImage problem
              IP47ATVR101.03.201700:06NightChecked
              IP72TVR201.03.201700:32NightChecked
              IP47ATVR101.03.201700:39NightImage problem
              IP55CTVR201.03.201700:55NightChecked
              IP47ATVR101.03.201701:11NightImage problem
              IP2TVR101.03.201701:20NightImage problem
              IP20TVR101.03.201701:38NightChecked
              IP20TVR101.03.201701:47NightImage problem
              IP2TVR101.03.201701:56NightChecked
              IP76TVR301.03.201702:23NightImage problem
              IP2TVR101.03.201702:39NightImage problem
              IP26TVR101.03.201702:59NightEmergency situation
              IP55CTVR201.03.201703:19NightImage problem
              IP1ETVR201.03.201704:03NightImage problem
              IP28ATVR101.03.201704:32NightImage problem
              IP55CTVR201.03.201704:34NightChecked
              IP55CTVR201.03.201705:03NightChecked
              IP20TVR101.03.201705:08NightChecked
              IP55CTVR201.03.201705:14NightChecked
              IP40TVR401.03.201705:43DayImage problem
              IP76TVR201.03.201705:48DayChecked
              IP55CTVR201.03.201705:50DayChecked
              IP20TVR101.03.201706:11DayChecked
              IP55CTVR201.03.201706:16DayChecked
              IP2TVR301.03.201706:31DayYellow light
              IP55CTVR201.03.201706:37DayChecked
              IP55CTVR201.03.201706:39DayChecked
              IP40TVR401.03.201706:47DayChecked
              • Re: How can I list values including text format with a condition on a chart?
                Ozgur Cirak

                Hi Sunny,

                 

                I ve another bottleneck today.

                I have started to study aggr() function and made my first table with it. If you remember i had a database with TVR codes and violation profiles etc.


                Now i made a table with TVR Code dimension ;

                And another one for most repetitive unchecked volaton for each tvr and the script is :

                FirstSortedValue([Violation Profile],-aggr(count({<[Violation Profile]=-{'Checked'}>}[Violation Profile]),[TVR Code-Viol_March.TVR Code],[Violation Profile]))


                Now im trying to do a pivot table yet again with tvr codes as dimension. But now im adding months as columns and i want to calculate above expression monthly. Besides i need to calculate count value of that expression.


                So to summarize my main code is for mont repetitive issue : FirstSortedValue([Violation Profile],-aggr(count({<[Violation Profile]=-{'Checked'}>}[Violation Profile]),[TVR Code-Viol_March.TVR Code],[Violation Profile]))

                 

                What i want is most repetitive issue by month and its count value.

                 

                Can u help me?