Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Could you please resolve the issue
What is the Issue?
if there's duplicate records in table file
the use
load distinct fieldname
from your data source;
or for expression side
write in text box
= count(distinct duplicate record fieldname) //duplicate record fieldname is your fieldname
Please clear the requirement.
if you want to know about DISTINCT
Eg:-
ID
100
100
200
300
100
400
200
If you count this ID with the below script in any text box
Count(ID) = you get 7
and with DISTINCT see the result
Count(DISTINCT ID) = you get 4 which is unique.
I have three tables ,
Date Flightno Service used
and in service used there are two type of service So on a same date one Flight can used any number of services but i want to count as a one but another day same flight can avail the service but i want to count its another day service as one too. so its like for a month i want count total no of distinct flights used the services.
Create a straight Table
Dimensions
Month
Service
Expression
Count(Distinct Flight)
Create a composite key between fields like
Date&'-'&Flightno as DateAndFilghtNo
Date&'-'&Flightno&'-'&[Service used] as DateAndFilghtNoWithservice
and count those keys with DISTINCT or direct count
if you have sample then provide please.
date | flightno | service used | |
20131022005811 | B | PCA | |
20131022005811 | B | PCA | |
20131022005611 | C | GPU | |
20131022005811_ | D | GPU | |
20131022005812 | A | PCA | |
20131021005811_ | B | PCA | |
20131021005811_ | A | PCA | |
20131023005811_ | V | GPU | |
20131023005811_ | B | GPU | |
20131023005811_ | B | GPU | |
2013102300511 | C | GPU | |
20131023005811_ | D | GPU | |
20131024005811_ | A | GPU | |
20131024005811_ | B | GPU | |
20131024005811_ | A | GPU |
I want to calculate % total no of flight used Gpu = No of flight used GPU/Total no of Flights
condition is that any of entries of same flight on a same day should be count as 1 .
you can get the total number of flights as COUNT(DISTINCT flightNo)
according to your example Total number of flights would be 5
Create a straight table
Dimension = Date or Month
Expression
COUNT({<[service used] = {'GPU'}>}flightno)/Count(TOTAL flightno)
or
COUNT({<[service used] = {'GPU'}>}flightno)/Count(TOTAL DISTINCT flightno)