topic Comparing two fields on Set Analisys in QlikView

Comparing two fields on Set Analisys

Anonymous — Thu, 07 Dec 2017 18:48:17 GMT

Thats my data cloud:

TABLE: ATTENDANCE_REGISTER

COLUNS: "REG_NUMBER", "YEAR_MONTH_REGISTERED", "YEAR_MONTH_CLOSED", "%KEY_YEAR_MONTH_NUMBER"

TABLE: CLIENT

COLUNS: "CLI_NUMBER", "YEAR", "MONTH", "%KEY_YEAR_MONTH_NUMBER"

I wanna make a pivot table with these dimensions: "YEAR", "MONTH" and this expression: the quantity of ATTENDENCE_REGISTER that was registered and closed at the same year_month.

The Set Analisys i'm using is:

COUNT({<YEAR_MONTH_REGISTERED={'$(=YEAR_MONTH_CLOSED)'}>} REG_NUMBER)

But it doesnt work! Any one help me?

Thanks!

Re: Comparing two fields on Set Analisys

m_woolf — Thu, 07 Dec 2017 18:53:54 GMT

Try:

COUNT({<YEAR_MONTH_REGISTERED={YEAR_MONTH_CLOSED}>} REG_NUMBER)

Re: Comparing two fields on Set Analisys

Anil_Babu_Samineni — Fri, 08 Dec 2017 00:45:55 GMT

Sorry to say, syntax is wrong. Should be this

If(YEAR_MONTH_REGISTERED=YEAR_MONTH_CLOSED, 1,0) as flag

And expression is

Count({<flag = {1}>} REG_NUMBER)

COUNT({<YEAR_MONTH_REGISTERED={"=YEAR_MONTH_REGISTERED

=YEAR_MONTH_CLOSED>} REG_NUMBER)

Or finally

Count(if(YEAR_MONTH_REGISTER= YEAR_MONTH_CLOSED, REG_NUMBER))