Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am using a chart to display the count of some records. I have a condition where I need to display the sum of records if one date = another.
Consider this example.
Name | Date1 | Date2 |
A | 4/10/2015 | 4/10/2015 |
B | 4/10/2015 | 4/10/2015 |
C | 4/10/2015 | 4/11/2015 |
D | 4/11/2015 | 4/12/2015 |
E | 4/11/2015 | 4/12/2015 |
I need the output as
Date | Count |
4/10/2015 | 2 |
4/11/2015 | 0 |
4/12/2015 | 0 |
Count of 2 includes records A and B because Date1=Date2. And 0 for the other records.
Could you please advise how this can be done?
Thanks in advance.
Hi,
If both the fields are in the same table then you can do it in script easily like below
Data:
Load
Name,
Date1,
Date2,
If(Date1=Date2,1, 0) As DateFlag
From DataSource;
Now in front end you can use
Dimension: Date1
Expression: Sum(DateFlag)
If both the fields are in different tables then try like below
Dimension: Date1
Expression: Sum(Aggr(If(Date1= Date2, 1, 0), Date1, Date2))
Hope this helps you.
Regards,
Jagan.
Hi Varun,
I would do this comparison in the script to create a flag, which you can then easily use within set analysis.
Load
Name,
Date1,
Date2,
If(Date1=Date2,1) As MatchedDateFlag
From.....
Then in your expression simply
Count( {<MatchedDateFlag={1}>} Date1)
hope that helps
Joe
Hi,
Please try below code:
Test:
LOAD *,if(Date1=Date2,'1','0') as flag Inline
[
Name,Date1,Date2
A,4/10/2015,4/10/2015
B,4/10/2015,4/10/2015
C,4/10/2015,4/11/2015
D,4/11/2015,4/12/2015
E,4/11/2015,4/12/2015
];
Table2:
LOAD sum(flag) as count,
Date2 as Date
Resident Test
Group by Date2;
HTH
Sushil
Hi,
PFA
Thanks
BKC
Hi,
If both the fields are in the same table then you can do it in script easily like below
Data:
Load
Name,
Date1,
Date2,
If(Date1=Date2,1, 0) As DateFlag
From DataSource;
Now in front end you can use
Dimension: Date1
Expression: Sum(DateFlag)
If both the fields are in different tables then try like below
Dimension: Date1
Expression: Sum(Aggr(If(Date1= Date2, 1, 0), Date1, Date2))
Hope this helps you.
Regards,
Jagan.
Hi Joe,
The two fields are from different tables and hence I did not use this method. I used Jagan's method and the results were accurate.
Thanks for your quick response. Really appreciate it.
Hi Jagan,
This method worked fine. The fields were indeed from two different tables.
Thanks for the quick response.