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

[himabinduch]

Hi Anil,

i dint get you exactly could you please brief me  

[effinty2112]

Hi Hima,

Maybe:

RawData:

LOAD * INLINE [

    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

];

Data:

LOAD

Item,

Item_creation as DateTime,

'Creation' as Event

Resident RawData;

LOAD

Item,

item_shipped as DateTime,

'Shipped' as Event

Resident RawData;

Drop Table RawData;

Then this straight table with calculated dimension QuarterName(DateTime):

=QuarterName(DateTime)	Count({$<Event = {'Creation'}>}Item)	Count({$<Event = {'Shipped'}>}Item)
	25	25
Jan-Mar 2016	24	0
Jul-Sep 2016	0	5
Oct-Dec 2016	0	12
Jan-Mar 2017	1	3
Apr-Jun 2017	0	2
Jul-Sep 2017	0	3

EDIT:

=QuarterName(DateTime)	Count(DISTINCT{$<Event = {'Creation'}>}Item)	Count({$<Event = {'Shipped'}>}Item)
	3	25
Jan-Mar 2016	2	0
Jul-Sep 2016	0	5
Oct-Dec 2016	0	12
Jan-Mar 2017	1	3
Apr-Jun 2017	0	2
Jul-Sep 2017	0	3

Regards

Andrew

[Anil_Babu_Samineni]

Flags already setup by Andrew, you can look over his reply

[MarcoWedel]

Hi,

maybe another solution might be:

tabItems:
LOAD Item, 
     Timestamp#(Item_creation,'M/D/YYYY h:mm') as Item_creation,
     Timestamp#(item_shipped,'M/D/YYYY h:mm') as Item_shipped
FROM [https://community.qlik.com/thread/277641] (html, codepage is 1252, embedded labels, table is @1);

tabDateLink:
CrossTable (DateType, Date)
LOAD Item, 
     DayName(Item_creation) as Creation,
     DayName(Item_shipped) as Shipped
Resident tabItems;

tabCalendar:  
LOAD *,  
     Day(Date) as Day,  
     WeekDay(Date) as WeekDay,  
     Week(Date) as Week,  
     WeekName(Date) as WeekName,  
     Month(Date) as Month,  
     MonthName(Date) as MonthName,  
     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,  
     QuarterName(Date) as QuarterName,  
     Year(Date) as Year,  
     WeekYear(Date) as WeekYear;    
LOAD Date(MinDate+IterNo()-1) as Date  
While MinDate+IterNo()-1 <= MaxDate;  
LOAD Min(Date) as MinDate,  
     Max(Date) as MaxDate  
Resident tabDateLink;

see also:

Canonical Date

hope this helps

regards

Marco

[himabinduch]

Hi Andrew,

thanks for your help its is working fine but i have one problem now i have to create % of shipped items in different quarters so i used below expression

=(Count(DISTINCT{$<Event={'Shipped'}>}ITEM)/Count(DISTINCT{$<Event={'Creation'}>}ITEM))*100 & ' %'

But now I am facing some issue with this percentage some items are created in 2016 Q1 and shipped in 2017Q2 so now my chart should show the values for that particular items as below

For example

Item	count_item_creation	count_shipped_item	CUMMULATIVE%	quarter
cd89	1	0	0	2016Q1
CD89	1	0	0	2016Q2
cd89	1	0	0	2016Q3
cd89	1	0	0	2016Q4
cd89	1	0	0	2017Q1
cd89	1	1	1	2017Q2

[effinty2112]

HI,

Maybe this pivot table is what you're looking for:

Item	CD89		CD90		CD91
=QuarterName(DateTime)	Shipped	Shipped %	Shipped	Shipped %	Shipped	Shipped %
Jan-Mar 2017	2	28.57%			1	5.88%
Oct-Dec 2016	4	57.14%			8	47.06%
Apr-Jun 2017	1	14.29%			1	5.88%
Jul-Sep 2017			1	100.00%	2	11.76%
Jul-Sep 2016					5	29.41%

Shipped: Count({$<Event = {'Shipped'}>}Item)

Shipped % : Count({$<Event = {'Shipped'}>}Item)/Count({$<Event = {'Shipped'}>}TOTAL <Item> Item)

Regards

Andrew

[himabinduch]

Hi Andrew,

i need pivort like below

[himabinduch]

the exact one is as below here fg mcns setup is nothing but  created item count and Cumulative transactions is nothing but shipped item count

[himabinduch]

Hi Marco,

thank for your help but i need the data in pivot table and i need the count of create item and shipped item in the same quarter for example if i have 10 items created in 2017Q1 created items are 10 and it got shipped in 2018 Q1 it not suppose to show in any of the quarter even in 2018Q1

thanks,

hima

[vikasdesai]

Hello

Please use Following Expression

Count({<Quarter={$(=max(Quarter))}>}[FieldName])

Regards

Vikas