Using Aggr() and and DISTINCT and calculate average value

[magnusrydberg]

Hi,

I have a file with warehouse input transactions. I want to figure out how many different suppliers i receive goods from each day. Then I want in front end to calculate the average value for each month

 The file look like this

Transdate	Package	Supplier
20190101	1245654	Supplier 1
20190101	5242575	Supplier 2
20190101	5242576	Supplier 1
20190102	5242589	Supplier 3
20190104	5242564	Supplier 1
20190104	5242523	Supplier 1
20190105	5242509	Supplier 1
20190106	5242551	Supplier 5
20190106	5242523	Supplier 1

I'm trying in frontend to calculate how many different suppliers it is per transaction date by using the Aggr function and then calculate the average value for each day and present it per month:

Avg (Aggr(Count(Distinct Supplier),TransDate))

[JordyWegman]

Hi Magnus,

You are doing it good, except for the TransDate, which should be Transdate. If you make a month column Month(Date(Date#(Transdate,'YYYYMMDD'),'DD-MM-YYYY')), put this in a bar chart and get your correctly spelled formula, then it will work!

Jordy

Climber

[magnusrydberg]

Hi Jordy,

I'm afraid I did the wrong spelling in this question. In my expression in the it's spelled correct.

The problem for me is that it I've got the value of one for every day so the mean value for the month is also the value of one....

What am i doing wrong?

Thanks in advance

// Magnus

[magnusrydberg]

Hi Jordy,

I'm afraid I did the wrong spelling in this question. In my expression in the it's spelled correct.

The problem for me is that it I've got the value of one for every day so the mean value for the month is also the value of one....

What am i doing wrong?

Thanks in advance

Magnus

[JustinDallas]

Is there a way you could show us what your "correct" table would look like?  

[sunny_talwar]

What is the exact output you are hoping to see?

[magnusrydberg]

I want a result table like this :

and that the mean value for 201901 would be 2+1+1+1+1+2=7 divided with 5=1,4

Transdate	Number of suppler
20190101	2
20190102	1
20190104	1
20190105	1
20190106	2

I want the mean value for 201901 would be 2+1+1+1+1+2=7 divided with 5=1,4 🙂

Best regards

Magnus

[sunny_talwar]

In that case, your expression looks good to me... Are you using YearMonth as your dimension where you are not seeing 1.4 for 201901?

[magnusrydberg]

Yes I'm using YearMonth as the dimension 🙂

My expression in front end:

AVG (Aggr((Count(Distinct If (PBLCType='Via BLC' or LP_Supplier='AHLS_SU',LP_Supplier))),PBLCDate))

The dimesion is PYearMonth. 

My Sqript:

// Warehouse transactionsLOAD PackageNo as PPackageNo,
PackageNoSeq as PPackageNoSeq,
PackageNo&PackageNoSeq as PPackage,
TransactionDateTime as PTransactionDateTime,
FromCompany as PFromCompany,
if (FromCompany='$(CompanyConst2)','Via BLC','Direkt') as [PBLCType],
FromFacility as PFromFacility,
FromWarehouse as PFromWarehouse,
FromLocation as PFromLocation,
FromContainer as PFromContainer,
ToCompany as PToCompany,
ToFacility as PToFacility,
ToWarehouse as PToWarehouse ,
ToLocation as PToLocation,
ToContainer as PToContainer,
Date(CreatedDateTime) as PCreatedDate,
dual( Year(CreatedDateTime) & '-' & Month(CreatedDateTime), num(Year(CreatedDateTime)) & num(Month(CreatedDateTime), '00' )) as [PYearMonth],
dual( Year(CreatedDateTime) & '-' & Week(CreatedDateTime), num(Year(CreatedDateTime)) & num(Week(CreatedDateTime), '00' )) as [PYearWeek],
text(date(CreatedDateTime,'YYYY-MM')) as KPIYearMonth,
Year (CreatedDateTime) as [PYear],
Month(CreatedDateTime) as [PMonth],
WeekDay(CreatedDateTime) as [Pday],
Hour(CreatedDateTime) as [PHour];
SQL SELECT *FROM lsp.PackageTransactions where ToCompany='$(CompanyConst1)' and FromCompany<>'$(CompanyConst1)' and CreatedDateTime>'$(Datelimit)';

Inner Join
LOAD
Contractor as [PToCompany],
ProjectId as [PToFacility],
ProjectName as [PToProjectName];
SQL SELECT *
FROM lsp.ConstructionProject;

Inner Join
LOAD PackageNo as PPackageNo,
PackageNoSeq as PPackageNoSeq,
Description as PDescription,
Supplier as LP_Supplier,
if (BuildingId='AH','Avhämtat','Uppburet') as [PDelType],
BuildingId as PBuildingId,
PackageType as PPackageType,
DeliveryId as PDeliveryId,
ProjectSubId as PProjectSubId,
CreatedBy as PCreatedBy;
SQL SELECT * FROM lsp.Package;

Left Join
LOAD Person as PCreatedBy,
Company as PCompany;
SQL SELECT * FROM "myloc_PROD_220".cabase.CompanyPersons;

Inner Join
LOAD
Contractor as [PToCompany],
ProjectId as [PToFacility],
ProjectSubId as PProjectSubId,
ProjectSubId as KPIProjectSubId,
StatisticalGroupId as PStatisticalGroupId,
if(len(StatisticalGroupId)>0,StatisticalGroupId,'Saknas') as KPIStatisticalGroupId,
SubContractorId as PSubContractorID;
SQL SELECT *
FROM lsp.ConstructionProjectSubMaster;

Left Join
LOAD
PackageNo as PPackageNo,
PackageNoSeq as PPackageNoSeq,
PackageNo&PackageNoSeq as PPackage,
TransactionDateTime as PTransactionDateTime,
Date(CreatedDateTime) as PBLCDate;
SQL SELECT *FROM lsp.PackageTransactions where ToCompany='$(CompanyConst1)' and FromCompany='$(CompanyConst2)' and CreatedDateTime>'$(Datelimit)';

Hope you can help me 🙂

// Magnus