Dataset to interact with master calender but for previous year

[helen_pip]

Dear Qlikview user

I have an expression which allows my data to interact with selections from the master calender.  However I need to create an expression where the user is picking from the master calender but the expression is looking at the same time period but for exactly one year earlier

Count({$<FactDateNum ={">=$(=v_Start_Date_CWT)<=$(=v_End_Date_CWT)"}, Breach Indicator = {'N'}>} ID)

Can anyone kindly help me achieve this, by helping me amend my expression

Kind Regards

Helen

[sunny_talwar]

How do you define your variable today?

v_Start_Date_CWT

v_End_Date_CWT

[johnw]

Perhaps this?

count({<FactDateNum={"=FactDateNum>=addmonths(v_Start_Date_CWT,-12) and FactDateNum<=addmonths(v_End_Date_CWT,-12)"}, [Breach Indicator]={'N'}>} ID)

You might have trouble with other date fields linked to FactDateNum, and might need to do a bunch of Month=,Quarter=,Year=, etc. in the expression to ignore them.

[helen_pip]

Hello SunnyT

Apologies for the delay.  The dates are derived as follows:

v_Start_Date_CWT = Min({<data_source={'CWT'}>}FactDateNum)

v_End_Date_CWT  =Max({<data_source={'CWT'}>}FactDateNum)

The FactDateNum is created as follows:  num(Floor(FactDate)) as FactDateNum

Any help to get the exact same time period for the previous year would be greatly appreciated

Kind Regards

Helen

[sunny_talwar · Accepted solution]

May be try this:

Count({$<FactDateNum ={">=$(=Num(AddMonths(v_Start_Date_CWT, -12)))<=$(=Num(AddMonths(v_End_Date_CWT, -12)))"}, Breach Indicator = {'N'}>} ID)

[helen_pip]

Hello

Thank you for your response.   I shall have a play about with your suggestion and see if I can get it to work with my other data items

Kind Regards

Helen

[sunny_talwar]

Sounds like a plan. Also, do try John's proposed solution.

[helen_pip]

This worked for me!  A great breakthrough, so a big thank you!

Thanks

Helen

[helen_pip]

Hello Jon

Thank you for your suggestion....This is really helpful and has help me understand a lot more about how I can adapt my expressions to look at previous years data

Kind Regards

Helen

[helen_pip]

Hello John

You mention in your post that I may have problems with the Month, Quarter and Year fields, and your right I am

Our user has the option to choose from the master calender or from list boxes for Month, Quarter and Year.

If I did not want to ignore these fields and incorporate them, so the expression interathemcts with them also, would I have to use an if statement in my expression which revalautes the expressions, depending if the end user is interacting with the calendar or the Month, year and quarter fields

Kind Regards

Helen