Aggregation of a count

[AndrejLinde]

Hey there,

I have a file with orders and all the information of the customer and so on. Also there is a column telling the days the order is pending till today.

Now I want to show in Qlik sense in a chart (circle or bar) how many orders there are pending >6 days, pending <8 days and pending >8 days.

I do not need the exact count of how many orders there are pending for 2, 3 or 5 days. Thats what I already have. I need that aggregated. How do you get there?

Also it would be nice if I click on the bar "<6 days" that Qlik Sense shows me all the clients effected.

Thanks for any help!

[martinpohl]

use

count({<pendingdays={"<6"}>} orderno) 

count({<pendingdays={">=6<8"}>} orderno) 

and

count({<pendingdays={">8"}>} orderno) 

If you want it in a chart to select, calculate a field in script. When selected you can show a detail table

Regards

[AndrejLinde]

Hey Martin,

Thanks for your reply but thats what allready have (see the screenshot attached) This is for the function:

count({<[Tabelle1$-1.PendingDays]={"<6"}>} Orderno)

The function shows how many orders I have that are 0, 2,3,4,5 days old. I need that all together in one piece off the pie.

I want to see in one pie the count of all orders that are up to 5 days old, in the next pie all orders that are 6+7 days old and in the third piece all the orders that are older than 7 days.

So i need the sum off the count of orderno "<6" and so on.

[JGMDataAnalysis · Accepted solution]

Calculated Dimension:

If(PendingDays < 6, '< 6 Days', If(PendingDays < 8, '< 8 Days', '>= 8 Days'))

Or define the calculated dimension (Group) in script...

Test:
NOCONCATENATE 
LOAD *, 
	 If(PendingDays < 6, '< 6 Days', If(PendingDays < 8, '< 8 Days', '>= 8 Days')) AS Group
INLINE [
        OrderNo, PendingDays
        0001,	 15
        0002,	 13
        0003, 	 11
        0004, 	 9
        0005,  	 8
        0006, 	 6
        0007, 	 5
        0008, 	 3
        0009, 	 3
        0010, 	 1
];

Measure:

Count(distinct [OrderNo])