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

[johnw]

Great question! It leads us into the subtleties of what QlikView is doing as it tries to satisfy your request.

Let's say you select Month = April 2016, corresponding to FactDateNums between April 1, 2016 and April 30, 2016. But maybe for data_source 'CWT' the minimum is actually April 5, 2016, and maximum is April 22, 2016, so that's what's in your variables.

Now let's say you use Sunny T's or my expression for getting the previous year. It will get a FactDateNum between April 5, 2015 and April 22, 2015. However, none of those FactDateNums will match Month = April 2016, and so you'll get no data back. That's, I assume, the problem you're seeing.

But what happens if you add Month=,Quarter=,Year= as I suggested? Well, you're telling it to ignore the conflict with the Month selection and to trust FactDateNum, and so it will count IDs between April 5, 2015 and April 22, 2015. In other words, even though you're explicitly telling the set analysis to ignore selections in Month, your expression as a whole is still indirectly taking the selection into account when returning the data.

[helen_pip]

Hello John

Thank you for your explained response and patience

I have tried your suggestion and my expression is ignoring the fields Month, QuarterName and FiscalYear, but still interacts with FactDateNum

Count({$<Month,QuarterName,FiscalYear,FactDateNum={">=$(=Num(AddMonths(v_Start_Date_CWT, -12)))<=$(=Num(AddMonths(v_End_Date_CWT, -12)))"}>}ID)

Any further help would be greatly appreciated, but I will continue to work on adjusting my expression and trying to work it out

Kind Regards

[sunny_talwar]

It is still interacting based on FactDateNum is because your variable not ignoring any selection.

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

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

If you don't want your expression to change even based on selection in FactDateNum selection, then add this to your variables

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

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

[helen_pip]

Hello John

You kindly helped me a few weeks ago with this query and explained some subtleties around how qlikview is calculating dates

I have tried yours and SunnyT suggestions, but with no success.  I have written an expression which is as follows: 

This expression interacts with my master calendar and my field Month, Year and Quarter

=Count({$<FactDateNum ={">=$(=v_Start_Date_CWT)<=$(=v_End_Date_CWT)"}>}ID)

However the previous year calculation interacts with the Master Calendar, but not with Month, Year and Quarter

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

I tried your suggestion to see what happens when I add the following fields to the expression, and as suggested my expression ignores these fields

=Count({$<Month,QuarterName,FiscalYear,FactDateNum={">=$(=Num(AddMonths(v_Start_Date_CWT, -12)))<=$(=Num(AddMonths(v_End_Date_CWT, -12)))"}>}ID)

However my aim is for the expression to interact with the master calendar and the 3 fields.  Is there anyway O can achieve this:

FactDateNum is derived from a date field and coverts the date into a numbers and my variables are as follows:

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

I also tried SunnyT's idea of putting a 1 in the variable expression and as suggested the 1 made sure that the expression only interacted with FactDateNum

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

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

Any Help or advice would be greatly appreciated

Kind Regards

Helen

[johnw]

It seems I don't understand precisely what you mean by words like "interacts with". One thing that would help me a lot would be if you posted an example QVW with an inline load of some sample data, example selections, and an explanation of what resulting chart and numbers in the chart you want to see for that data and those selections.

[helen_pip]

Hi John

Thanks for your response. I shall put an example together (May take a little while) and will reply in due course

I really appreciate your help

Thanks

Helen