count with conditional group by

[emilyrabbit]

Hi buddy, I have below request, no idea how to create the expression. Thanks for any suggestion.

I have a table: [Year Month],[Sales Admin],[Order]

I want to calculate the active user ( the user should have >1 order) number in each month.The example excel is attached.

The final chart what I want.

I want to write the expression in the chart , not in the loading script.

=count({<aggr((count(distinct[Order]),[Year Month],[Sales Admin])>1)>}distinct[Sales Admin])

@hanna_choi 

[hanna_choi]

Hi @emilyrabbit 

Would you like to change the script like below?

****************************

Data_Table:
Load
Orders&Date as Key,
*
;
LOAD
Text(Date("Order Header Creation Date", 'YYYYMM')) as Date,
"Order Document Number" as Orders,
"Order Created By Rep" as Rep
FROM ~~~~
where _KEY_Unit_DWHK='2'
;

TEMP3:
NoConcatenate
Load
Key,
Count(DISTINCT Orders) as COUNTNUM
Resident Data_Table
Group BY Key
;

[emilyrabbit]

[hanna_choi]

Hi @emilyrabbit 

The error occurred in the TEMP4 table, and the key field is lowercase.

In the TEMP3 table, the first letter of the Key field is uppercase.

[emilyrabbit]

HI @hanna_choi   maybe a stupid question, I don't understand the yellow part. Thanks for any information from your side.

[hanna_choi · Accepted solution]

Hi @emilyrabbit 

I had mistake.
The expression is wrong.

[emilyrabbit]

@hanna_choi  Hi 😊 I met another issue, in this case , we think the active use is :in each month , the user should have >1 order  . Could I design a expresssion ,treat the 1 to be variable ? Thanks. That means, the end user can select the order number by themselves.

[hanna_choi]

Hi @emilyrabbit 

Do you want to see the chart according to the change in the number of orders?

Or do you want to see that include one order?

[emilyrabbit]

hi @hanna_choi I found the solution, create a variable.😊 Thanks anyway.