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]

Hello @emilyrabbit 

Expression : 
Count( DISTINCT Aggr( IF(Count({<[Year Month]>} Order) > 1, [Sales Admin]), [Year Month], [Sales Admin] ) )

Notice :

The use of if syntax within a set analysis can cause performance issue.

If you have a lot of data, the solution is group by in data modeling.

[Amit_Prajapati]

@emilyrabbit ,

You can create a flag in the script by counting Orders grouped by YearMonth and Sales Admin. Then use that flag in your set expression like this:

count({<Flag={'Yes'}>} Order)

This makes your expression faster and more efficient.

[anat]

load YM,Sales,Order from table;

load YM,Sales,count(Order) as Nooforders resident group by YM,Sales;

then in frontend use Nooforders >1

[emilyrabbit]

TEMP3:
NoConcatenate
Load
[Order Created By Rep]&Year([Order Header Creation Date]) &Num(Month([Order Header Creation Date]),00) as key,
Count(DISTINCT ([Order Document Number])) as COUNTNUM
from *** (qvd)
where _KEY_Unit_DWHK='2'
Group BY [Order Created By Rep],(Year([Order Header Creation Date]) &Num(Month([Order Header Creation Date]),00));

HI , I tried in the loading script. and [Order Header Creation Date] is a date format, I need to transform it to YYYYMM , the loading is failed .It shows invalid expression. Could you pls help me checking it ?Thanks.