topic Re: Using Aggr() and and DISTINCT and calculate average value in App Development

Using Aggr() and and DISTINCT and calculate average value

magnusrydberg — Sat, 16 Nov 2024 20:54:27 GMT

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

Re: Using Aggr() and and DISTINCT and calculate average value

JordyWegman — Thu, 09 May 2019 13:45:01 GMT

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

Re: Using Aggr() and and DISTINCT and calculate average value

magnusrydberg — Thu, 09 May 2019 13:52:52 GMT

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

Re: Using Aggr() and and DISTINCT and calculate average value

magnusrydberg — Thu, 09 May 2019 13:53:51 GMT

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

Re: Using Aggr() and and DISTINCT and calculate average value

JustinDallas — Thu, 09 May 2019 13:54:03 GMT

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

Re: Using Aggr() and and DISTINCT and calculate average value

sunny_talwar — Thu, 09 May 2019 14:10:50 GMT

What is the exact output you are hoping to see?

Re: Using Aggr() and and DISTINCT and calculate average value

magnusrydberg — Thu, 09 May 2019 14:11:04 GMT

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

Re: Using Aggr() and and DISTINCT and calculate average value

sunny_talwar — Thu, 09 May 2019 14:18:08 GMT

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?

Re: Using Aggr() and and DISTINCT and calculate average value

magnusrydberg — Thu, 09 May 2019 14:38:07 GMT

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