Aggr Function

[himabinduch]

Hi All,

i have 3 fields as Item, Item_creation date and item_shipped date one item can have multiple Shipped date but only one creation date.

now i want the data as based on the quarter i need count of created items and shipped items the data is as below

Item	Item_creation	item_shipped
CD89	1/8/2016 15:46	11/22/2016 20:16
CD89	1/8/2016 15:46	12/6/2016 20:17
CD89	1/8/2016 15:46	12/13/2016 20:17
CD89	1/8/2016 15:46	12/20/2016 20:16
CD89	1/8/2016 15:46	3/21/2017 21:15
CD89	1/8/2016 15:46	3/21/2017 21:15
CD89	1/8/2016 15:46	6/20/2017 21:16
CD90	3/8/2017 10:54	7/11/2017 23:46
CD91	1/8/2016 15:46	8/3/2016 21:16
CD91	1/8/2016 15:46	8/23/2016 21:16
CD91	1/8/2016 15:46	9/7/2016 21:15
CD91	1/8/2016 15:46	9/12/2016 21:16
CD91	1/8/2016 15:46	9/21/2016 21:16
CD91	1/8/2016 15:46	10/12/2016 21:16
CD91	1/8/2016 15:46	11/1/2016 0:01
CD91	1/8/2016 15:46	11/1/2016 0:01
CD91	1/8/2016 15:46	11/29/2016 20:17
CD91	1/8/2016 15:46	12/5/2016 2:47
CD91	1/8/2016 15:46	12/18/2016 20:17
CD91	1/8/2016 15:46	12/20/2016 20:16
CD91	1/8/2016 15:46	12/28/2016 20:17
CD91	1/8/2016 15:46	3/1/2017 20:15
CD91	1/8/2016 15:46	4/7/2017 1:04
CD91	1/8/2016 15:46	7/5/2017 21:16
CD91	1/8/2016 15:46	7/14/2017 3:27

[isorinrusu]

Best thing you can do is to create a new field containing the quarter, based on the creation or shipped date. Or 2 fields for each one of them.

The function is QuarterName().

[Anil_Babu_Samineni]

From which Date field we need to en count the Quarters? Can you explain result set if you have

[himabinduch]

i created quarters based on shipped and creation dates. but i need i need count of created items and shipped items

[himabinduch]

on created and shipped both fields we should use.

[isorinrusu]

Then you only have to create a simple table (for example), using the created quarter fields as dimensions and a count(distinct Item) as the exrpression.

[himabinduch]

count( distinct item) will give the created item counts, i already done with that, i got stucked in shipping count of items. if you see my data there are different shipped_dates for each item so i have to calculate the count of shipped_items based on the quarter .

[isorinrusu]

Ok, I think I understand. So you don't want a chart, you want a KPI or something like that?

Then you'd have to use AGGR, as you're mentioning. Something like:

sum(aggr(count(Item),ShippedQuarter))

That will give you the number of items shipped per quarter.

Hope it helps.

Regards,

Sorin.

[Anil_Babu_Samineni]

This is the case you need to set up flag while loading to club single field. And that too in analysis you can use Flag for bifurcate reports

[himabinduch]

i want create a pivort chart with the above expressiones