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

[Anonymous]

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

[sunny_talwar]

Continuous knowledge from Qlik? Not sure what that is?

[Anonymous]

İ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?

[sunny_talwar]

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

[Anonymous]

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?

[Anonymous]

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?

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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.

[sunny_talwar]

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