YTD, PYTD,MTD,PMTD expressions help required.

[#1]

Hi Friends,

I have below expressions in if-else conditions. Please help me to write the same in SET Analysis.

1) Count(if(Status='Draft' and Type='Type1' and MatterID=NULL() and CREATE_DATE=CALENDER_DATE,Incident_ID))

2) Count(if(Status<>'Draft' and Type='Type1' and MatterID=NULL() and CREATE_DATE=CALENDER_DATE,Incident_ID))

Dear friends, I want to write the above expressions in Year To Date, Previous Year To Date, Month To Date and Previous Month To Date.

I tried with below for one. But it is not working. I don't know where to put and CREATE_DATE=CALENDER_DATE condition.

PYTD:

Count({<Year=, Month=, Quarter=, CALENDAR_DATE=, DateNum={">=$(=Num(YearStart(Max(DateNum), -1)))<=$(=AddYears(Max(DateNum), -1))"}>} Incident_ID)

Please help. Very Very Very urgent requirement.

[ElizaF]

Hello,

Please used the below page as a reference and adapted to suit your scenario.

It is a stable solution and easy to be implement.

QlikView App: Set Analysis - Prior Period Comparison

Hope it is useful to you.

[shraddha_g]

You have missed ')' in set analysis

YTD

Count({<Year=, Month=, Quarter=, CALENDAR_DATE=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}, Status = {'Draft'},Type={'Type1'},MatterID={NULL()}>} Incident_ID)

PYYTD:

Count({<Year=, Month=, Quarter=, CALENDAR_DATE=, DateNum={">=$(=Num(YearStart(Max(DateNum),-1)))<=$(=AddYears(Max(DateNum), -1)))"}, Status = {'Draft'},Type={'Type1'},MatterID={NULL()}>} Incident_ID)

[#4]

Dear Shraddha,

I have two dates. One CREATE DATE coming from the fact tale and another one CALENDAR DATE coming from the master calendar table. And my expression contains a condition “AND CREATE_DATE=CALENDER_DATE”

I don't know where to put “AND CREATE_DATE=CALENDER_DATE” in Set Analysis condition.

[shraddha_g]

How your CALENDAR_DATE and DateNum are related?

are the calculated from same Field?

[#6]

Dear Shraddha,

I have two dates. One CREATE DATE coming from the fact tale and another one CALENDAR DATE coming from the master calendar table. And my expression contains a condition “AND CREATE_DATE=CALENDER_DATE”

I don't know where to put “AND CREATE_DATE=CALENDER_DATE” in Set Analysis condition.

Could you please include “AND CREATE_DATE=CALENDER_DATE” in the set analysis expression and repost?

Or is it something like this?,

Count({<Year=, Month=, Quarter=, CREATE_DATE=CALENDER_DATE, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}, Status = {'Draft'},Type={'Type1'},MatterID={NULL()}>} Incident_ID)

[#7]

Yes Shraddha,

CALENDAR DATE and DATE NUM are calculated from the same field Calendar Date

[#8]

Yes dear...

CALENDAR DATE and DATE Num are calculated from the same field Calendar Date.

[shraddha_g]

then you can do this.

Try:

YTD

Count({<Year=, Month=, Quarter=, CREATE_DATE={">=$(=Num(YearStart(Max(CALENDER_DATE))))<=$(=Max(CALENDER_DATE”))"}, Status = {'Draft'},Type={'Type1'},MatterID={NULL()}>} Incident_ID)

PYYTD:

Count({<Year=, Month=, Quarter=, CREATE_DATE={">=$(=Num(YearStart(Max(CALENDER_DATE),-1)))<=$(=AddYears(Max(CALENDER_DATE), -1)))"}, Status = {'Draft'},Type={'Type1'},MatterID={NULL()}>} Incident_ID)

[Anonymous]

Hi,

You can go through below link.

Hope this helps you :

Calendar with flags making set analysis so very simple