# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for
Did you mean:
Not applicable

## HELP! Aggregate a Subset of a Deminsion

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
1 Solution

Accepted Solutions
MVP

Hi,

for unequal sized buckets another solution could be:

```tabTickets:
Ceil(NORMINV((Rand()+1)/2,0,15)) as [Days Open]
AutoGenerate 1000;

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

Left Join (tabTickets)
IntervalMatch ([Days Open])
Resident tabOpenClass;

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

DROP Table tabOpenClass;
DROP Field RangeMin, RangeMax;
```

hope this helps

regards

Marco

18 Replies
MVP

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%','.',',')

Partner

Hi John,

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

Regards,

Chris

Partner

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.

Not applicable
Author

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

jt

Partner

Regards,

Jagan.

MVP

Hi,

for unequal sized buckets another solution could be:

```tabTickets:
Ceil(NORMINV((Rand()+1)/2,0,15)) as [Days Open]
AutoGenerate 1000;

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

Left Join (tabTickets)
IntervalMatch ([Days Open])
Resident tabOpenClass;

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

DROP Table tabOpenClass;
DROP Field RangeMin, RangeMax;
```

hope this helps

regards

Marco

MVP

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.

MVP

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

MVP

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?