topic Using 2 aggregate functions in Water Cooler

Using 2 aggregate functions

harveenkaur — Wed, 28 Nov 2018 06:41:29 GMT

I want to count cities having sum(Quantity)>1000 and sum(Sales)>1200 for the maximum month year in Date( In sample Data :- Sept. 2018)

City	Quantity	Sales(Rs)	Date
C1	1000	2000	12-Sep-18
C2	12312	12310	13-Sep-18
C3	121	21223	12-May-18
C4	1212	1313	30-Aug-17
C1	1212	12123	20-Sep-18
C2	121	1313	08-Nov-18
C3	121	132	09-Nov-17

Re: Using 2 aggregate functions

PradeepReddy — Wed, 28 Nov 2018 07:01:20 GMT

Create a calculated field in script like this...
Load *,
Date(monthstart(Date#(Date,'DD-MMM-YY')),'YYYY-MMM') as YearMonth
From TableName;

Create straight table, with
dimension : YearMonth
expression: Count(Distinct {<Quantity={'>=1000'},[Sales(Rs)]={'>1200'}>}City)

Re: Using 2 aggregate functions

harveenkaur — Wed, 28 Nov 2018 08:21:01 GMT

I have YearMonth field but don't want to use it as dimension.
I want it to be included in the expression
expression: Count(Distinct {<Quantity={'>=1000'},[Sales(Rs)]={'>1200'}>}City)

Re: Using 2 aggregate functions

PradeepReddy — Thu, 29 Nov 2018 02:10:39 GMT

can you post the sample output, from the data you provided earlier?

Re: Using 2 aggregate functions

harveenkaur — Thu, 29 Nov 2018 03:53:14 GMT

Output will have:-

City

I want only City as Field.