topic Re: Query for comparison within the dimension in QlikView

Query for comparison within the dimension

Wed, 25 Nov 2020 16:16:04 GMT

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

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

Re: Query for comparison within the dimension

tresB — Mon, 22 May 2017 11:40:40 GMT

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

115, 12/01/2016

115, 10/01/2016]

Expression used like:

=Concat( DISTINCT {

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

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

} ECI)

Re: Query for comparison within the dimension

Mon, 22 May 2017 12:03:39 GMT

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.

Re: Query for comparison within the dimension

Mon, 22 May 2017 12:06:06 GMT

I replaced concat with count. It works.
thanks

Re: Query for comparison within the dimension

Tue, 23 May 2017 07:39:47 GMT

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.

Cheers,

Rohan