Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
cskqlikview
Contributor II
Contributor II

How to count no of records

  I have the data in the fact table as follows

YEARMONTHMETRICS_IDTERMINAL_IID
201603611
201603612
201603621
201603621
201603612
201603612
201603112
201603112
201603121
201603121
201603111
201603112
201603221
201603211
201603212
201603221
201603212
201603212
201603312
201603312
201603312
201603521
201603512
201603512
201603512
201603521
2016035

11

We have the following dimension

 

METRICS_ID
1
2
3

4

5

6

The expected results.

When the User Select Metrics_ID 6 from Dimension , we need to count (terminal_iid) who had done 2 or more transactions rest for all other metrics we need to count(terminal_iid)    

for example if user select metrics_id 6 and year 2016 and month 03 , then the expected results should be 2.  (Only Terminal IID 12 and 21 having 2 and more transactions)

I am new and not familiar with set analysis, is there any way to build the expression for the above case.

Please guide me

5 Replies
sunny_talwar

May be this:

=Sum(If(Aggr(Count(TERMINAL_IID), METRICS_ID, TERMINAL_IID) > 1, 1))

or

=Sum(If(Aggr(Count(TERMINAL_IID), METRICS_ID, TERMINAL_IID, YEAR, MONTH) > 1, 1))


Capture.PNG

cskqlikview
Contributor II
Contributor II
Author

Thanks a lot  for you reply.  I have tried the same , but still i am getting 3 for Metrics_ID for 6.  I have uploaded your QVW file , there i can see

    

YEARMONTHMETRICS_IDTERMINAL_IID
201603111
201603112
201603121
201603211
201603212
201603221
201603312
201603511
201603512
201603521
201603611
201603612
201603621

i can see only 3 records for metrics ID 6.  Is anything we need to do to restrict the original data?.   Because the original data i have 6 Rows for metrics ID 6

Please advice

sunny_talwar

I am not 100% sure I understand where are you seeing 3? My screenshot above shows 2 for  METRICS_ID = 6. Do you want to see 3?

cskqlikview
Contributor II
Contributor II
Author

Sorry its working fine .. Its resolved

sunny_talwar

Awesome. I would suggest closing the thread down in that case. Also Please consider marking real helpful answer as helpful. As marking your response doesn't really seems helpful for future users.

Thanks,

Sunny