Solved: HELP! Aggregate a Subset of a Deminsion - Qlik Community

Report Inappropriate Content · ‎2016-01-12

Please help I have a simple list of Helpdesk Tickets where I am trying to determine % of Tickets Open 1 day or less, % of Tickets Open 2 - 5 days etc. I can not seem to get an expression to work where the Count of "Tickets Open" = 1 / Count of "Ticket # gives me a %. Syntax for this is very frustrating given I can handle simple math / algebra.

Ticket #	Days Open
19821	1
19822	1
19824	1
19825	1
19826	1
19827	3
19828	4
19829	4
19831	4
19832	5
19833	5
19834	5
19837	6
19838	6
19841	8
19842	9

MarcoWedel · ‎2016-01-17

Hi,

for unequal sized buckets another solution could be:

tabTickets:
LOAD 10000+RecNo() as [Ticket #],
    Ceil(NORMINV((Rand()+1)/2,0,15)) as [Days Open]
AutoGenerate 1000;

tabOpenClass:
LOAD RangeMin,
    Alt(Peek(RangeMin)-1, Dual('∞', 100000000000000)) as RangeMax
Inline [
RangeMin
50
30
20
15
10
5
1
];

Left Join (tabTickets)
IntervalMatch ([Days Open])
LOAD RangeMin, RangeMax
Resident tabOpenClass;

Left Join (tabTickets)
LOAD Distinct
    RangeMin, RangeMax,
    Dual(RangeMin&'-'&RangeMax, RangeMin) as [Days Open Class]
Resident tabTickets;

DROP Table tabOpenClass;
DROP Field RangeMin, RangeMax;

hope this helps

regards

Marco

View solution in original post

swuehl · ‎2016-01-12

You can start with creating a chart with dimension field [Day Open], then use as expression

=Count([Ticket #])

and enable relative checkbox on expression tab, or alternatively use as expression

=Num( Count([Ticket #]) / Count(TOTAL [Ticket #]) , '#.00%','.',',')

cn_sa_dev · ‎2016-01-13

Hi John,

If the intervals are fixed you can use class() as per attachment.

Regards,

Chris

jagan · ‎2016-01-13

HI,

You can also try like this

Chart : Straight Table

Dimension: [Days Open]

Expression: Count([Ticket #])

and select Relative option in Expressions tab.

Regards,

Jagan.

Report Inappropriate Content · ‎2016-01-15

Thank you this got me closer to what I am trying to accomplish.

jt

jagan · ‎2016-01-17

If you got the Answer close this thread by giving Correct and Helpful Answers to the posts which helps you in getting the answer.

Regards,

Jagan.

MarcoWedel · ‎2016-01-17

Hi,

for unequal sized buckets another solution could be:

tabTickets:
LOAD 10000+RecNo() as [Ticket #],
    Ceil(NORMINV((Rand()+1)/2,0,15)) as [Days Open]
AutoGenerate 1000;

tabOpenClass:
LOAD RangeMin,
    Alt(Peek(RangeMin)-1, Dual('∞', 100000000000000)) as RangeMax
Inline [
RangeMin
50
30
20
15
10
5
1
];

Left Join (tabTickets)
IntervalMatch ([Days Open])
LOAD RangeMin, RangeMax
Resident tabOpenClass;

Left Join (tabTickets)
LOAD Distinct
    RangeMin, RangeMax,
    Dual(RangeMin&'-'&RangeMax, RangeMin) as [Days Open Class]
Resident tabTickets;

DROP Table tabOpenClass;
DROP Field RangeMin, RangeMax;

hope this helps

regards

Marco

sunny_talwar · ‎2016-01-17

MarcoWedel‌ just out of curiosity, did you use NORMINV to get a random number which follow normal distribution? I have never used it and was curious what it means.

MarcoWedel · ‎2016-01-17

Hi sunindia‌,

you're right it's just been to get some nice sample data other than equal distributed (normal distributed instead, yes).

It's been mentioned by Henric here for example: https://community.qlik.com/blogs/qlikviewdesignblog/2013/08/26/monte-carlo-methods#comment-8831

regards

Marco

sunny_talwar · ‎2016-01-17

Awesome, thanks Marco‌. I always hated that whenever I create a random distribution it was equally distributed. Being able to generate normal distribution would be great. Do you know if there are other distributions that we can create?