Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld May 10-12, Online and Free! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Harish_Qlik_user
Contributor II
Contributor II

Need to show a chart with count of items with amount greater than at each country

Hi,

I'm looking for showing count of records which are more than 1 million for each country when aggregating at Code,Country,Vendor,posting date.

Below is the sample date  and my actual data has more columns than below.

CodeCountryDateVendorAmount
U1UAE07.09.20202514900056498.6413
U1UAE07.09.2020137306817023142354.33
U2UAE07.09.2020137761615537350000
U2UAE07.09.202013777630652486616.848
U1UAE07.09.202013778651551425998.641
U2UAE07.09.202013781415451663320
U2UAE07.09.202013820072708868696.603
U2UAE07.09.20201387515150936179.1653
U2UAE07.09.20201390505640319707.8804
U2UAE07.09.202013978660804191725.543
I1IND07.09.202014074413757913598.913
I1IND07.09.2020140860669510328110.64
I1IND07.09.202014129007001245000
I1IND07.09.202014166207601758393.342
I2IND07.09.20201416874740996000
I2IND07.09.20201416882210996000
I2IND07.09.202014176976852642918.478
I3IND07.09.2020141832641032338502.3
I3IND07.09.2020141970213565097128.25
I3IND07.09.20201420685685821091.0326
S2SG07.09.20201422037755808911.6848
S2SG07.09.2020142342842096889112.9
S2SG07.09.2020142479418531059975.82
S2SG07.09.2020142653345076703304.6
S2SG14.09.202030630735222949.7283
S3SG14.09.202031806015247646.7391
S1SG14.09.202013732275308982404.348
S2SG14.09.202013766238905116019.633
S3SG14.09.202013777244701182750
S1SG14.09.2020137789005516234055.71
S2SG14.09.2020137789503513950630.98
G1GB14.09.202013779373651231751.576
G1GB14.09.20201377984675808911.6848
G2GB14.09.20201378116645308881.7935
G2GB14.09.202013783033951182750
G2GB14.09.202013797824551691576.087
G3GB14.09.202013941261001315190.258

 

I need to show a graph with count of records at country level group by below columns and sum of amount > 1million

CodeCountryDateVendor

 

so below is how it should show count at each country ,  sum amount >1 million with aggregation at code, date, vendor for each country.

CountryCount
GB2
IND4
SG5
UAE6

 

11 Replies
avinashelite

Try like this 

Dimension: Country

Expression = count({<Amount={">1000000"}>}Amount)

Harish_Qlik_user
Contributor II
Contributor II
Author

thanks for reply but this will just filter the data for amount greater than million but I need sum(amount)>1m grouped at code,Posting date,vendor,

Usama
Creator
Creator

Hi,

try this:
Count({<Amount={">1000000"}>}Code)
this will give you count across all fields you mentioned

Thanks

From Nothing - To Something - To Everything
Kush
MVP
MVP

@Harish_Qlik_user  How it is 2 for GB?

Harish_Qlik_user
Contributor II
Contributor II
Author

@Kush  I just have random numbers. I was trying to show how the final output should be.

 

Kush
MVP
MVP

@Harish_Qlik_user  Then what @avinashelite  suggested should work. If not share the expected output for the above data

Harish_Qlik_user
Contributor II
Contributor II
Author

@Kush @avinashelite please find the sample file.

Kush
MVP
MVP

@Harish_Qlik_user  see below

Screenshot 2020-10-06 143552.png

 

avinashelite

@Harish_Qlik_user  as per the excel output above mentioned expression will give you the desired results , not sure what your exactly looking for ?