18 Replies Latest reply: Jan 21, 2016 2:51 PM by Onno van Knotsenburg

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

• ###### Re: HELP! Aggregate a Subset of a Deminsion

Hi John,

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

Regards,

Chris

• ###### 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.

• ###### Re: HELP! Aggregate a Subset of a Deminsion

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

jt

• ###### Re: HELP! Aggregate a Subset of a Deminsion

Regards,

Jagan.

• ###### 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

• ###### Re: HELP! Aggregate a Subset of a Deminsion

Marco 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.

• ###### Re: HELP! Aggregate a Subset of a Deminsion

Hi Sunny,

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

• ###### 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?

• ###### Re: HELP! Aggregate a Subset of a Deminsion

Actually just checked his blog and I saw that we do have T, F, chi, and other options available. Thanks for the link to the blog.

• ###### Re: HELP! Aggregate a Subset of a Deminsion

Thank you Marco...

• ###### Re: HELP! Aggregate a Subset of a Deminsion

You're welcome

Thanks

regards

Marco

• ###### Re: HELP! Aggregate a Subset of a Deminsion

I have looked everywhere for a button to close this thread and cannot find it anywhere. Would you be so kind as to point me in the direction of where this is at or send me a screen shot. I have looked everywhere and it is not obvious. Thank you.

jt

• ###### Re: HELP! Aggregate a Subset of a Deminsion

Hi,

you have to e.g. mark an answer correct to close a thread:

thanks

regards

Marco

• ###### Re: HELP! Aggregate a Subset of a Deminsion

I give up. I do not see the Correct Button anywhere.

• ###### Re: HELP! Aggregate a Subset of a Deminsion

If you can't see it under every post, something might be wrong.

Tell me which post (name and date) you want to flag correct, and I will do it for you.

Onno

Qlik Community Team member (and Qlik Senior Technical Trainer in my spare time ).

• ###### Re: HELP! Aggregate a Subset of a Deminsion

Name of post is :

HELP! Aggregate a Subset of a Deminsion<https://community.qlik.com/message/949710#949710>

Post Date is:

Jan 12, 2016 6:00 PM

Thank you very much Onno and my apologies for being so much trouble.

jt

• ###### Re: HELP! Aggregate a Subset of a Deminsion

I think that is your own openings post.