QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: HELP! Aggregate a Subset of a Deminsion

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

Re: HELP! Aggregate a Subset of a Deminsion

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

New Contributor III

Re: HELP! Aggregate a Subset of a Deminsion

Hi John,

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

Regards,

Chris

MVP

Re: HELP! Aggregate a Subset of a Deminsion

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

Re: HELP! Aggregate a Subset of a Deminsion

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

jt

MVP

Regards,

Jagan.

MVP

Re: HELP! Aggregate a Subset of a Deminsion

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

Re: HELP! Aggregate a Subset of a Deminsion

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

Re: HELP! Aggregate a Subset of a Deminsion

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

Re: HELP! Aggregate a Subset of a Deminsion

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?