# Qlik Sense App Development

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

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

Labels (1)

8 Replies
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

## 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?

// Magnus

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?

Magnus

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?

MVP

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

What is the exact output you are hoping to see?

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

 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

MVP

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

## 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
Contractor as [PToCompany],
ProjectId as [PToFacility],
ProjectName as [PToProjectName];
SQL SELECT *
FROM lsp.ConstructionProject;

Inner Join
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
Company as PCompany;
SQL SELECT * FROM "myloc_PROD_220".cabase.CompanyPersons;

Inner Join
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