Line Item Wise count to find Amount (Query Designer)

[anubhav_kumar2]

Hello

I have a requirement wherein I have to find Amount for each Equipment by dividing it by the count of distinct equipments containing per CRM OBJ ID.

CRM OBJ ID	EQUIPMENT No.	Net Price	What I Require
6955031	17884	10000	10000/3
	17884	10000	10000/3
	17884	10000	10000/3
	21557	21000	21000/2
	21557	21000	21000/2
	9574	24000	24000
6209830	17884	10000	10000
	13554	11000	11000
	4324	18000	18000/3
	4324	18000	18000/3
	4324	18000	18000/3

As you can see above, for example, CRM OBJ ID 6955031 has three 17884 equipments with 10000 net price... the amount I require should be 10000/3. Again, 9574 is just one equipment... so its amount remains 24000/1 = 24000.

Also, another CRM OBJ ID 6209830 also contains one 17884 equipment... but since this equipment is not related to the previous 17884 occurrences, the amount must remain 17884/1 = 17884.

How is this possible in Query Designer? Kindly help.

[former_member186053]

Hi Anubhav,

First you need to get line item count under a CRM OBJ ID, you need to create two formulas with exception aggregation option to get count. Create formula F1 by inserting any KF with exception aggregation count of all detailed values that are not zero or null with reference characterstic CRM OBJ ID. Second formula F2 by inserting first formula F1 with exception aggregation as summation with reference Equipment no.

Finally use this count in a formula net price/ count.

Hope this helps.

Regards,

Vengal