Need a set analysis expression

[woshua5550]

Hi everybody,

I have 2 tables like this 

Table1

Relationship

All I want to do is to create a straight table with Model and ReportMonth dimension, and count the number of VIN1/VIN2 for each Model when WSMonth = ReportMonth

expect result :

Model	ReportMonth	Count VIN1	Count VIN2
A	201901	1	0
B	201901	1	1
B	201902	1	1

what should my expression looks like ?

Thanks !

Dave

[Arthur_Fong]

Try joining these 2 tables together:

Remove the set analysis at the second expression.

Refer script as below:

LOAD * Inline [
Model,ReportMonth,VIN1
A,201901,V1
B,201901,V2
B,201902,V3
];
JOIN
LOAD Model,WSMonth AS ReportMonth,VIN2 Inline [
Model,WSMonth,VIN2
B,201901,V4
B,201902,V5
];

Thanks and regards,

Arthur Fong

[woshua5550]

Hi Arthur 

Thanks, but I'm afraid we can't do the join because the 2 tables stands for different business meanings, you can consider that table1 is stock information and table2 is sales information

calculation goes wrong  when table2 becomes like this :

expected result :

Model	ReportMonth	Count VIN1	Count VIN2
A	201901	1	0
B	201901	1	2
B	201902	1	1

BRs

Dave

[Arthur_Fong · Accepted solution]

If join is not a good option, perhaps concatenating both tables will solve the problem:

Refer qvw attached below as reference.

Thanks and regards,

Arthur Fong

[jaibau1993]

Hi! Set analysis is evaluated once per chart. This means that you can't build a "row-wise set analysis" expression but it is what you need: for each value of the dimension "ReportMonth" you need to count VIN2 where WSMonth equals the respective value of ReportMonth. Said so and given that you can not modify the script (by the way, are you sure you cant? You dont neet to join nor concatenate both tables, you maybe can create an auxiliar field that helps you solve your problem) I suggest you to use an "if" expression:

Count(if(WSMonth = ReportMonth, VIN2))

since it is row wise (but it is less efficient than set analysis so be careful).

Regards,

Jaime.