Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Partner
Partner

Using Aggr() and and DISTINCT and calculate average value

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

TransdatePackageSupplier
201901011245654Supplier 1
201901015242575

Supplier 2

201901015242576

Supplier 1

201901025242589

Supplier 3

201901045242564

Supplier 1

201901045242523

Supplier 1

201901055242509

Supplier 1

201901065242551

Supplier 5

201901065242523

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

 

8 Replies
Partner
Partner

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

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

Work smarter, not harder
Partner
Partner

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

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

Partner
Partner

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

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
Valued Contributor II

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

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

What is the exact output you are hoping to see?

Partner
Partner

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

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

TransdateNumber of suppler
20190101 2
201901021
201901041
201901051
201901062

 

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

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?

Partner
Partner

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

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