Calculated Pie Chart Dimension?

[tonymakos]

Hi guys - first time poster, long time reader 🙂 Got a quandary i need some help on...

Trying to put together a pie chart that shows the following: 

How many customers have placed X amount of orders before a dynamic date. I need the slices of the pies showing how many customers (a count of their reference numbers) placed 1 order, 2 orders, 3 orders, etc.

So this involves first calculating how many orders were actually placed before that date, then using those number counts as the dimension of the pie chart, where it should hopefully show how many distinct customer reference numbers placed those orders.

My issues is with the calculated dimension: i can't get it to work. What am i doing wrong?

The dimension should be calculated by something *like* the following: =(count(if([order_date_created]<[specific_date_field],order_id))). That will give the numbers i then need to check reference IDs with - how many IDs only placed 1 order for example?

I have a pivot table that shows reference ids as the dimension, and the above order counts as the value, but i can't turn this into a chart.

Any help would be amazing!

[QFabian]

Hi @tonymakos , please check this example.

//First, script with some sample data 

Data:
Load * INLINE [
CustomerID, OrderID, Date
a , 1 , 01-01-2023
a , 2 , 02-01-2023
a , 3 , 03-01-2023
a , 4 , 04-01-2023
a , 5 , 07-01-2023
a , 6 , 10-01-2023
a , 7 , 12-01-2023
a , 8 , 13-01-2023
a , 9 , 13-01-2023
a , 10 , 14-01-2023
a , 11 , 16-01-2023
a , 12 , 21-01-2023
a , 13 , 21-01-2023
a , 14 , 24-01-2023
a , 15 , 26-01-2023
a , 16 , 29-01-2023
a , 17 , 30-01-2023
b , 1 , 01-01-2023
b , 2 , 05-01-2023
b , 3 , 07-01-2023
b , 4 , 09-01-2023
b , 5 , 11-01-2023
b , 6 , 12-01-2023
b , 7 , 16-01-2023
b , 8 , 19-01-2023
b , 9 , 29-01-2023
b , 10 , 31-01-2023
b , 11 , 31-01-2023
c , 1 , 07-01-2023
c , 2 , 16-01-2023
c , 3 , 17-01-2023
c , 4 , 17-01-2023
c , 5 , 17-01-2023
c , 6 , 17-01-2023
c , 7 , 25-01-2023
d , 1 , 01-01-2023
d , 2 , 02-01-2023
d , 3 , 03-01-2023
d , 4 , 04-01-2023
d , 5 , 07-01-2023
d , 6 , 10-01-2023
d , 7 , 12-01-2023
d , 8 , 13-01-2023
d , 9 , 13-01-2023
d , 10 , 14-01-2023
d , 11 , 16-01-2023
d , 12 , 21-01-2023
d , 13 , 21-01-2023
d , 14 , 24-01-2023
d , 15 , 26-01-2023
d , 16 , 29-01-2023
d , 17 , 30-01-2023
];

// the following script, is going to accumulate quantity of orders, using peek function

Result:
Load
CustomerID,
Date,
OrderID,
if(CustomerID <> previous(CustomerID), 1, peek(OrdersByCustomer) + 1) as OrdersByCustomer
Resident Data
Order by
CustomerID,
Date;

drop table Data;
exit script;

Then, the chart uses OrdersByCustomer as dimension and as expression : Count(distinct CustomerID)

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecor...

[tonymakos]

Thank you for your help! The thing is, the OrdersByCustomer count needs to be dynamic depending on the date of the order compared against a separate date - it should only be counted if the [date_of_order] is less than that [separate_date]. Where should that condition be inserted? Also, does the script need to be inserted alongside my other scripts in the load editor? The data for it comes from two separate tables...

[QFabian · Accepted solution]

Please try this, no need additional script 

just formula for dimension : =aggr(count({< OrderDate = {"<=$(=max(OrderDate))"} >} OrderID), CustomerID)

measure expression : Count(distinct CustomerID)

[tonymakos]

Once again thanks fot your help on this. It worked a treat once I figured out the pie chart settings 🙂 This community is great!

[QFabian]

thank you @tonymakos , good job

[tonymakos]

Well, turns out this wasn't bringing up the results I was expecting, and after a day or so of trying to figure out why, i've returned to the forum seeking some further assistance!

In the formula cited above

=aggr(count({< OrderDate = {"<=$(=max(OrderDate))"} >} OrderID), CustomerID)

the part in quotes puzzles me. I need to be able to insert a date field which the report is currently filtering the report on (lets call it FilterDate) so that the pie chart will aggregate the number of customer IDs that have X amount of orders that took place before that FilterDate. So I tried this:

=aggr(count({< OrderDate = {"<=$(=max(FilterDate))"} >} OrderID), CustomerID)

and while it produces a good looking graph i've checked the data behind the scenes and it's incorrect. What is the reason for the quotes, and the Max function? Maybe i can figure it out if i know why this approach doesnt work for me. Once again, many thanks in advance

[QFabian]

the max is if you have selected more than one value for the field 'OrderDate' or the name that have in your data, it picks the max value, to avoid  the formula to fail.

the double quotes are for passing a search for the condition.

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAn...