Qlik Community

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

Accepted Solutions
MarcoWedel

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

View solution in original post

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

cn_sa_dev
Partner
Partner

Hi John,

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

temp.jpg

Regards,

Chris

jagan
Partner
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

jagan
Partner
Partner

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

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

sunny_talwar

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

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

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?