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: 
Not applicable

Query for comparison within the dimension

My database has 2 dimensions and the sample data is as follows:

ECI  Date

111 11/01/2016

111 12/01/2016

113 11/01/2016

113 11/01/2016

115 12/01/2016

115 10/01/2016

So I want to identify only those values in ECI which has appeared in the previous month too?

Therefore the answer for this would be 111.

Any suggestions on the formula?

Cheers,

Rohan Shetty

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Create a MonthName field (would specially be required when you would have to compare inter-year values)  in the script like:

Load *,

  Month(Date) as Month,

  MonthName(Date) as MonthName

Inline [

ECI,  Date

111, 11/01/2016

111, 12/01/2016

113, 11/01/2016

113, 11/01/2016

115, 12/01/2016

115, 10/01/2016]

Capture.PNG

Expression used like:

=Concat( DISTINCT {

  <ECI=p({<MonthName={'$(=MonthName(Max(Date)))'}>}ECI)>

  *

  <ECI=p({<MonthName={'$(=MonthName(Max(Date),-1))'}>}ECI)>

  } ECI)

View solution in original post

4 Replies
tresesco
MVP
MVP

Create a MonthName field (would specially be required when you would have to compare inter-year values)  in the script like:

Load *,

  Month(Date) as Month,

  MonthName(Date) as MonthName

Inline [

ECI,  Date

111, 11/01/2016

111, 12/01/2016

113, 11/01/2016

113, 11/01/2016

115, 12/01/2016

115, 10/01/2016]

Capture.PNG

Expression used like:

=Concat( DISTINCT {

  <ECI=p({<MonthName={'$(=MonthName(Max(Date)))'}>}ECI)>

  *

  <ECI=p({<MonthName={'$(=MonthName(Max(Date),-1))'}>}ECI)>

  } ECI)

Not applicable
Author

Hi tresesco,

The expression u provided works perfectly but I need a count of the total ECI rather than the final value.

I am sorry for the mistake I made while raising this question.

Not applicable
Author

I replaced concat with count. It works.
thanks

Not applicable
Author

Just another clarification

I have another field in the same table but it is unable to filter the data according to the field.

I have attached the sample

I understand from the data that 2964 and 4499 are present in all the Customer Data Quality dimension.

But only in CIP City is it occurring every month

I hope I am able to explain it clearly.

q2.JPG

Cheers,

Rohan