Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
harveenkaur
Contributor III
Contributor III

Using 2 aggregate functions

Hi

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)

CityQuantitySales(Rs)Date
C11000200012-Sep-18
C2123121231013-Sep-18
C31212122312-May-18
C41212131330-Aug-17
C112121212320-Sep-18
C2121131308-Nov-18
C312113209-Nov-17
4 Replies
PradeepReddy
Specialist II
Specialist II

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)
harveenkaur
Contributor III
Contributor III
Author

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)
PradeepReddy
Specialist II
Specialist II

can you post the sample output, from the data you provided earlier?
harveenkaur
Contributor III
Contributor III
Author

Output will have:-

City

C1

C2

I want only City as Field.