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.