Qlik Community

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
198211
198221
198241
198251
198261
198273
198284
198294
198314
198325
198335
198345
198376
198386
198418
198429
1 Solution

Accepted Solutions

Re: HELP! Aggregate a Subset of a Deminsion

Hi,

for unequal sized buckets another solution could be:

QlikCommunity_Thread_199308_Pic1.JPG

QlikCommunity_Thread_199308_Pic2.JPG

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

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

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

temp.jpg

Regards,

Chris

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

Re: HELP! Aggregate a Subset of a Deminsion

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.

Re: HELP! Aggregate a Subset of a Deminsion

Hi,

for unequal sized buckets another solution could be:

QlikCommunity_Thread_199308_Pic1.JPG

QlikCommunity_Thread_199308_Pic2.JPG

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

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.

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

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?

Community Browser