topic Re: Get count(Problems) within a period in QlikView

Get count(Problems) within a period

5abi0001 — Thu, 15 Feb 2018 10:25:56 GMT

Hey!

I have some issues, where i need ur help!

What i want to do is: I have a table with Groupnames which are linked to a ProblemNumber - furthermore i have a CreatedDate and ClosedDate (both of them are in numberic format). Furthermore i have two Variables vFromDate and vToDate. With these variables i can seth the range of the data i want to look at. For example if vFromDate is 2018-01-01 and vToDate is 2018-01-31 - i can see all problems which are created within this period. Now what i want is, to get only these groups and the number of problems they where linked to within this period. Based on them i want to now the top 10 groups and want to color them in a different colour in my diagram, because i have 20 groups and to colour them is a little bit annoying.

Re: Get count(Problems) within a period

YoussefBelloum — Thu, 15 Feb 2018 10:46:04 GMT

Hi,

maybe you need to use the Rank() function and apply it to your existing expression.

can you add more details, like the expression used, the type of objects you use ?

Re: Get count(Problems) within a period

5abi0001 — Thu, 15 Feb 2018 11:54:59 GMT

Hey, i have no existing expression 😕 .. i uploaded u a testfile.

Re: Get count(Problems) within a period

YoussefBelloum — Thu, 15 Feb 2018 12:52:59 GMT

Your expression is: Count({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} DISTINCT ProblemID)

Please find attached.

the result if on the column of the red table

Re: Get count(Problems) within a period

5abi0001 — Thu, 15 Feb 2018 12:58:25 GMT

Hey, super! This is exact what im looking for .. now i want to give them based on their rank colours in the diagram (Formula, Backgroundcolor) .. for the first 10 i want to give spezific colors and for the rest it should be a green tone which will be brighther, if the rank goes to 20! 🙂

Re: Get count(Problems) within a period

YoussefBelloum — Thu, 15 Feb 2018 13:14:21 GMT

ok, i should then let all the lines appear, because now I'm limiting the view for only the TOP 10 for each Group and for each Min/Max dates.

I'll send you another version

Re: Get count(Problems) within a period

5abi0001 — Thu, 15 Feb 2018 13:24:05 GMT

Okey, i will be waiting! 🙂 Thank u in advance.

Re: Get count(Problems) within a period

YoussefBelloum — Thu, 15 Feb 2018 13:35:10 GMT

Please find attached

Re: Get count(Problems) within a period

5abi0001 — Thu, 15 Feb 2018 14:11:38 GMT

Hey i think my requirement was not clear enough 😕 .. i want to color the dimension group in ma diagram CH03 in these colours based on the ranking.

Re: Get count(Problems) within a period

YoussefBelloum — Thu, 15 Feb 2018 16:01:29 GMT

Please find attached

Let me know

Re: Get count(Problems) within a period

5abi0001 — Fri, 16 Feb 2018 12:03:25 GMT

Yes, this i exact what want i want. I try to do this in my final work, which i cant upload. In the diagram i have the date dimension and the group dimension .. in my formula i count the problemid .. in backgroundcolor i triy to use the rank function like if(rank(count(problemid)) = 1, rgb(0,0,0)), but it does not work ..

Re: Get count(Problems) within a period

5abi0001 — Fri, 16 Feb 2018 13:18:32 GMT

Now it works but it its a little bit awkward .. because i have the follwing formula in the backround colour in my diagram =if(Group=firstgroup, rgb(255,255,255), rgb(0, 0, 0))

firstgroup is a variable with the following statement:

=aggr(if(Rank(count(problemid))=1,group),group)

It only works in this way .. but now i have to create 10 variables with the same statement .. this can not be the only solution, or?

Re: Get count(Problems) within a period

5abi0001 — Mon, 19 Feb 2018 12:03:41 GMT

Maybe someone know a better solution?