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.

[hanna_choi]

Hello @emilyrabbit 

Date data is the most difficult to convert.
The Date function depends on the date type of the source data.

Can I get that data shared?

[emilyrabbit]

Hi Sir, pls see attached, I grab some orders as example:

I received the request: I should create a chart : X-axis : Year Month(202510,202511...) Y-axis:

1/ distinct(count[Order Created By Rep])

2/distinct(count[Order Created By Rep])-- when distinct(count[Order document number])>1,because sometimes some part-time sales admin will do help ,we won't treat it as 'common admin'

as you recommend , I also think it is better that we do the tag in the loading script.

So,I tried below script:

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));

Pls help me check it. Thanks so much.

[hanna_choi]

Hi @emilyrabbit 

The date data was not the problem.

The dimension criteria used in the Group BY section are invalid.

There was a typo in the Group By clause.

************** 
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));

[emilyrabbit]

edited the script , still shows 'invalid expression'

[hanna_choi]

Hi @emilyrabbit 

What happens if you change the script as below

Will a key field be created

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

TEMP3:
NoConcatenate
Load
[Order Created By Rep]&Year("Order Header Creation Date") &Num(Month("Order Header Creation Date"), 00) as key
FROM ~~~~
where _KEY_Unit_DWHK='2'
;