topic Re: How can I list values including text format with a condition on a chart? in App Development

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

Anonymous — Tue, 20 Jun 2017 14:36:29 GMT

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 — Tue, 20 Jun 2017 15:42:11 GMT

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?

Anonymous — Tue, 20 Jun 2017 18:55:32 GMT

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 Code	Date	Time	Day Cycle	Violation Profile
IP55CTVR2	01.03.2017	00:03	Night	Image problem
IP47ATVR1	01.03.2017	00:06	Night	Checked
IP72TVR2	01.03.2017	00:32	Night	Checked
IP47ATVR1	01.03.2017	00:39	Night	Image problem
IP55CTVR2	01.03.2017	00:55	Night	Checked
IP47ATVR1	01.03.2017	01:11	Night	Image problem
IP2TVR1	01.03.2017	01:20	Night	Image problem
IP20TVR1	01.03.2017	01:38	Night	Checked
IP20TVR1	01.03.2017	01:47	Night	Image problem
IP2TVR1	01.03.2017	01:56	Night	Checked
IP76TVR3	01.03.2017	02:23	Night	Image problem
IP2TVR1	01.03.2017	02:39	Night	Image problem
IP26TVR1	01.03.2017	02:59	Night	Emergency situation
IP55CTVR2	01.03.2017	03:19	Night	Image problem
IP1ETVR2	01.03.2017	04:03	Night	Image problem
IP28ATVR1	01.03.2017	04:32	Night	Image problem
IP55CTVR2	01.03.2017	04:34	Night	Checked
IP55CTVR2	01.03.2017	05:03	Night	Checked
IP20TVR1	01.03.2017	05:08	Night	Checked
IP55CTVR2	01.03.2017	05:14	Night	Checked
IP40TVR4	01.03.2017	05:43	Day	Image problem
IP76TVR2	01.03.2017	05:48	Day	Checked
IP55CTVR2	01.03.2017	05:50	Day	Checked
IP20TVR1	01.03.2017	06:11	Day	Checked
IP55CTVR2	01.03.2017	06:16	Day	Checked
IP2TVR3	01.03.2017	06:31	Day	Yellow light
IP55CTVR2	01.03.2017	06:37	Day	Checked
IP55CTVR2	01.03.2017	06:39	Day	Checked
IP40TVR4	01.03.2017	06:47	Day	Checked

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

Anonymous — Tue, 20 Jun 2017 19:02:54 GMT

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 Code	Date	Time	Day Cycle	Violation Profile
IP55CTVR2	01.03.2017	00:03	Night	Image problem
IP47ATVR1	01.03.2017	00:06	Night	Checked
IP72TVR2	01.03.2017	00:32	Night	Checked
IP47ATVR1	01.03.2017	00:39	Night	Image problem
IP55CTVR2	01.03.2017	00:55	Night	Checked
IP47ATVR1	01.03.2017	01:11	Night	Image problem
IP2TVR1	01.03.2017	01:20	Night	Image problem
IP20TVR1	01.03.2017	01:38	Night	Checked
IP20TVR1	01.03.2017	01:47	Night	Image problem
IP2TVR1	01.03.2017	01:56	Night	Checked
IP76TVR3	01.03.2017	02:23	Night	Image problem
IP2TVR1	01.03.2017	02:39	Night	Image problem
IP26TVR1	01.03.2017	02:59	Night	Emergency situation
IP55CTVR2	01.03.2017	03:19	Night	Image problem
IP1ETVR2	01.03.2017	04:03	Night	Image problem
IP28ATVR1	01.03.2017	04:32	Night	Image problem
IP55CTVR2	01.03.2017	04:34	Night	Checked
IP55CTVR2	01.03.2017	05:03	Night	Checked
IP20TVR1	01.03.2017	05:08	Night	Checked
IP55CTVR2	01.03.2017	05:14	Night	Checked
IP40TVR4	01.03.2017	05:43	Day	Image problem
IP76TVR2	01.03.2017	05:48	Day	Checked
IP55CTVR2	01.03.2017	05:50	Day	Checked
IP20TVR1	01.03.2017	06:11	Day	Checked
IP55CTVR2	01.03.2017	06:16	Day	Checked
IP2TVR3	01.03.2017	06:31	Day	Yellow light
IP55CTVR2	01.03.2017	06:37	Day	Checked
IP55CTVR2	01.03.2017	06:39	Day	Checked
IP40TVR4	01.03.2017	06:47	Day	Checked

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

sunny_talwar — Wed, 21 Jun 2017 02:14:39 GMT

Are you looking to get this?

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])

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

Anonymous — Wed, 21 Jun 2017 05:48:25 GMT

Thank you Sunny,

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

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

sunny_talwar — Wed, 21 Jun 2017 08:29:19 GMT

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

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

Anonymous — Wed, 21 Jun 2017 11:16:35 GMT

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

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

sunny_talwar — Wed, 21 Jun 2017 11:18:26 GMT

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

Best,

Sunny

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

Anonymous — Wed, 21 Jun 2017 11:23:15 GMT

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?

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

sunny_talwar — Wed, 21 Jun 2017 11:26:38 GMT

Continuous knowledge from Qlik? Not sure what that is?

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

Anonymous — Wed, 21 Jun 2017 11:29:40 GMT

İn Qlik websitei there are training options. and one of them is continuous education. I can take courses whenever and wherever i want.

Topics are nice. But i dont know if it will worth price(1.400$)

Also i have found some books on amazon like Qlik sense cookbook etc.

Can you advice my anything about developing my skills on QLİK?

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

sunny_talwar — Wed, 21 Jun 2017 11:33:32 GMT

I have actually not gotten a chance to take those training sessions, but I am sure there is a lot of good information available in it for a beginner like you.

On the other hand, if you really don't have that kind of money, you can learn a lot from being an active participant on the community. You will be amazed to see your learning go up exponentially just by spending 1-2 hours every day by helping others and looking at other peoples responses.

Best,

Sunny

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

Anonymous — Thu, 22 Jun 2017 13:23:33 GMT

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?

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

Anonymous — Thu, 22 Jun 2017 13:25:14 GMT

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]))

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

Can u help me?

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

sunny_talwar — Thu, 22 Jun 2017 13:53:16 GMT

What is your Month field called? May be you forgot to add it to your Aggr() function here

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

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

Anonymous — Thu, 22 Jun 2017 14:00:29 GMT

Ok this was the first part, by the time I asked you, i had figured out:)

So how can i found the COUNT of : FirstSortedValue([Violation Profile], -Aggr(Count({<[Violation Profile]=-{'Checked'}>}[Violation Profile]), [TVR Code-Viol_March.TVR Code], [Violation Profile], MonthFieldHere))

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

sunny_talwar — Thu, 22 Jun 2017 14:02:44 GMT

May be this

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

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

Anonymous — Thu, 22 Jun 2017 14:48:06 GMT

Its making a calculation but it doesnt seems to be right.

To give an example, TVR code IP59TVR1 has green signal issue for april and may with a count of 500.

In the table : FirstSortedValue([Violation Profile], -Aggr(Count({<[Violation Profile]=-{'Checked'}>}[Violation Profile]), [TVR Code-Viol_March.TVR Code], [Violation Profile], MonthFieldHere)) works fine with the issue name.

But when it comes to : Sum(Aggr(If(FirstSortedValue([Violation Profile], -Aggr(Count({<[Violation Profile]=-{'Checked'}>}[Violation Profile]), [TVR Code-Viol_March.TVR Code], [Violation Profile], MonthFieldHere)) = [Violation Profile], 1, 0), [TVR Code-Viol_March.TVR Code], [Violation Profile], MonthFieldHere))

It makes a calculation and result is 15.

So here my logic

1)To find most repetitive issue type for all TVR Codes - Done

2)To find most repetitive issue type for all TVR Codes by month - Done

3)To find how many times that issue type occured during that month.-???

If most repetitive issue is green signal for IP59TVR1 and its count is 500 in march i want to see that value in my pivot table.

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

sunny_talwar — Thu, 22 Jun 2017 15:00:55 GMT

Is there a unique identifier field in your dashboard which we might use to count?