topic Re: Aggr Function in QlikView

Aggr Function

himabinduch — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggr Function

isorinrusu — Wed, 11 Oct 2017 10:03:31 GMT

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().

Re: Aggr Function

Anil_Babu_Samineni — Wed, 11 Oct 2017 10:05:15 GMT

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

Re: Aggr Function

himabinduch — Wed, 11 Oct 2017 10:21:40 GMT

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

Re: Aggr Function

himabinduch — Wed, 11 Oct 2017 10:24:23 GMT

on created and shipped both fields we should use.

Re: Aggr Function

isorinrusu — Wed, 11 Oct 2017 10:27:05 GMT

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.

Re: Aggr Function

himabinduch — Wed, 11 Oct 2017 10:36:17 GMT

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 .

Re: Aggr Function

isorinrusu — Wed, 11 Oct 2017 10:40:00 GMT

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.

Re: Aggr Function

Anil_Babu_Samineni — Wed, 11 Oct 2017 10:41:26 GMT

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

Re: Aggr Function

himabinduch — Wed, 11 Oct 2017 11:27:04 GMT

i want create a pivort chart with the above expressiones

Re: Aggr Function

himabinduch — Fri, 13 Oct 2017 05:36:46 GMT

Hi Anil,

i dint get you exactly could you please brief me

Re: Aggr Function

effinty2112 — Fri, 13 Oct 2017 09:36:21 GMT

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, 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/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

Re: Aggr Function

Anil_Babu_Samineni — Sun, 15 Oct 2017 10:44:25 GMT

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

Re: Aggr Function

MarcoWedel — Sun, 15 Oct 2017 19:47:06 GMT

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;

Re: Aggr Function

himabinduch — Mon, 16 Oct 2017 08:12:55 GMT

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

Re: Aggr Function

effinty2112 — Mon, 16 Oct 2017 09:06:50 GMT

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

Re: Aggr Function

himabinduch — Mon, 16 Oct 2017 09:57:16 GMT

Hi Andrew,

i need pivort like below

Re: Aggr Function

himabinduch — Mon, 16 Oct 2017 10:14:23 GMT

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

Re: Aggr Function

himabinduch — Tue, 24 Oct 2017 08:46:12 GMT

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

Re: Aggr Function

vikasdesai — Tue, 24 Oct 2017 10:31:22 GMT

Hello

Please use Following Expression

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

Regards

Vikas