Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

1 Solution

Accepted Solutions
sunny_talwar

I have attached two ways it can be done in this qlikview sample

1)

Sum(Aggr(If(FirstSortedValue(Makes, -Aggr(Count(Makes), Lanes, Makes)) = Makes, 1, 0), Lanes, Makes, RowNo))

Where RowNo was the UniqueIdentifier created in the script

Table:

LOAD *,

  RowNo() as RowNo;

LOAD * INLINE [

    Lanes, Makes

    1, Opel

    2, Honda

    3, Ford

    2, BMW

    3, Mercedes

    1, Ford

    2, Opel

    3, Mercedes

    2, BMW

    3, Mercedes

    1, Ford

    2, BMW

    3, Honda

];

2)

Count(Aggr(If(FirstSortedValue(Makes, -Aggr(Count(Makes), Lanes, Makes)) = Makes, Makes, 0), Lanes, Makes))

This can be translated to yours like this (putting it down in a minute)

Count(Aggr(If(FirstSortedValue([Violation Profile], -Aggr(Count({<[Violation Profile]=-{'Checked'}>}[Violation Profile]), [TVR Code-Viol_March.TVR Code], [Violation Profile], MonthFieldHere)) = [Violation Profile], [Violation Profile]), [TVR Code-Viol_March.TVR Code], [Violation Profile], MonthFieldHere))

View solution in original post

25 Replies
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?

Anonymous
Not applicable
Author

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
Anonymous
Not applicable
Author

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
sunny_talwar

Are you looking to get this?

Capture.PNG

Dimension

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

Expression

Count([Violation Profile])

Anonymous
Not applicable
Author

Thank you Sunny,

It works right know. Can i add additional condition to that expression?

sunny_talwar

Sure, what conditions do you wish to add in there?

Anonymous
Not applicable
Author

Actually, your reply is enough for me. I can edit your formula like :

=Aggr(If(Count({<[Violation Profile]={'Green signal issue'} AND another condition >}[Violation Profile]) > 1000, [TVR Code-Viol_March.TVR Code]), [TVR Code-Viol_March.TVR Code])


It can be percentage or green signal issues to total for example.


Thank you so much

sunny_talwar

That's great... I am glad you are willing to do this on your own.

Best,

Sunny

Anonymous
Not applicable
Author

I had no script language knowledge until i started to use QLİK sense. But with googling, community and youtube and some trying, i could manage to come up with results.

But when trying to extract data that depends on many variables, i can get stuck. I think about getting continous education from QLİK.

Will it be efficient for me?