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

[Anonymous]

This unique identifier changes upon TVR Codes. There are 6 issue type and most repetitive ones changes.

My goal is to calculate for example how many green signal for TVR code '1' in march,may at the same pivot table

Most Repetitive Issues
TVR Code	March	April	May
1	Green Signal	Yellow Light	Green Signal
2	BSL	Emergency	BSL
3	Image Problem	Green Signal	Emergency
4	Yellow Light	Yellow Light	Emergency
5	Emergency	BSL	Emergency
6	Image Problem	Image Problem	Yellow Light
7	BSL	Image Problem	BSL
8	Image Problem	Image Problem	Green Signal
9	Emergency	Yellow Light	Image Problem
10	Yellow Light	BSL	BSL

[sunny_talwar]

Not sure I understood that, but try 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], Count([Violation Profile]), 0), [TVR Code-Viol_March.TVR Code], [Violation Profile], MonthFieldHere))

[Anonymous]

Still its not working correctly, let me clarify for one last time with more generic example

For example i have three lane route : To find the most count make for each lane i use FirstSortedValue and  we find Ford for lane 1, BMW for lane 2 and Mercedes for lane 3.

My main goal is to create a pivot table like this and figure out how many cars of these makes passed through these lanes.

Lane	Make	Count
1	Ford	?
2	BMW	?
3	Mercedes  ?

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

Thank you so much Sunny,

It works like a charm.

[sunny_talwar]

Superb... please remember to close this thread down if you have achieved what you wanted.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny