topic Re: Sum of Distinct in QlikView

Sum of Distinct

Mon, 08 Jun 2015 11:29:41 GMT

I have a variable named as vTAT

I have to find the sum of vTAT based on DISTINCT MAR

MAR vTAT

123454 1:23

123456 4:15

1234566 5:17

Regards

Senthil

Re: Sum of Distinct

swuehl — Mon, 08 Jun 2015 11:31:18 GMT

Create a chart with dimension MAR and as expression

=sum(vTAT)

Or post your expected result..

Re: Sum of Distinct

PrashantSangle — Mon, 08 Jun 2015 11:39:00 GMT

or you can try

=sum(DISTINCT vTAT)

Re: Sum of Distinct

Mon, 08 Jun 2015 11:45:02 GMT

I want to show it in a BAR Chart with Dimension as Date , Expression as Avg of vTAT of DISTINCT MAR

Regards

Senthil

Re: Sum of Distinct

buzzy996 — Mon, 08 Jun 2015 11:46:28 GMT

se tis,

tat:

Load * INLINE [

MAR,vTAT

123454,1:23

123456,4:15

1234566,5:17

];

tat2:

nOCONCATENATE Load MAR,

SUM(vTAT) AS vTAT

resident tat

group by MAR;

Drop table tat;

Re: Sum of Distinct

Mon, 08 Jun 2015 11:46:46 GMT

I want to show it in a BAR Chart with Dimension as Date , Expression as Avg of vTAT of DISTINCT MAR

Re: Sum of Distinct

rubenmarin — Mon, 08 Jun 2015 11:51:53 GMT

Hi, vTat is a variable? and what's the expression of the variable? Maybe this works if vTAT has the same value for the same MAR:

Avg(Aggr(vTAT, MAR))

Re: Sum of Distinct

Mon, 08 Jun 2015 11:55:02 GMT

Yes vTAT is a Variable

Re: Sum of Distinct

Mon, 08 Jun 2015 12:02:15 GMT

Below is the sample data

Date	MAR	vTAT
2015-04-24	150424SCBLAU2SAXXX4495866032	1:13:41
2015-04-24	150424SCBLAU2SBXXX0123004002	63:41:27
2015-04-24	150424SCBLAU2SAXXX4495866033	65:52:23
2015-04-24	150424SCBLAU2SBXXX0123004002	63:41:27
2015-04-24	150424SCBLAU2SBXXX0123004002	63:41:27

Need to find the average of vTAT for DISTINCT MAR using Date as Dimension

Average should be around 43:35:50

Regards

Re: Sum of Distinct

rubenmarin — Mon, 08 Jun 2015 12:39:02 GMT

Hi, using "Avg(Aggr(vTAT, MAR))" returns 43:35:50, PFA.

Re: Sum of Distinct

Mon, 08 Jun 2015 12:40:30 GMT

Attachment Missing

Re: Sum of Distinct

rubenmarin — Mon, 08 Jun 2015 12:47:58 GMT

I see the "Test.qvw" in the previous post, anyway, I upload it again.

Re: Sum of Distinct

Mon, 08 Jun 2015 13:08:06 GMT

Below is my Requirement

Dimension - > Date

Need to find the average of Time Diff between EndDate and Start Date for Distinct MAR

In the Attached file the average should be 45:35:50

Note: Source file attached as excel in Distinct Sum

Regards,

Senthil