Set analysis for date 12m ago, with date also a dimension

[sarahallen1]

This feels like it should be simple but I can't for the life of me get to work!

I have 2 dimensions, AsOfDate and Fund.  I want 2 expressions: GBPValuation for the AsOfDate (easy), and GBPValuation for the date 12 months prior to the AsOfDate.  The second one is giving me problems.

A few complications:

1. Can't use a simple addmonths(AsOfDate,-12) function because GBPValuation data is not available on weekends and bank hols (really want to avoid 'filling' in these dates in the script).  For different funds, they observe different bank hols.  I started off using bank hol variables in the firstworkdate function in the expression, but it got v complex so I added these in the script instead, you can see it in the QVW attached.  The results are shown in the yellow table on the right in the script. So whilst this is a complication actually it should be simple now as I have a field AsOfDate_12m_Ago for each combination of Fund and AsOfDate.
2. There is another set modifier for pricingbasis=dealt.  Shouldn't cause issues?
3. Date formatting for AsOfDate_12m_Ago is default DD/MM/YYYY but AsOfDate originates from SQL and is YYYY-MM-DD.  I have another field AsOfDate_DateFormat which I put in to try to simplify things

I thought it should be something simple like this:

=sum({$<

  PricingBasis={"dealt"}  ,

  AsOfDate={'$(=AsOfDate_12m_Ago)'}>}

total <Fund>

GBPValuation)

And I've tried various combinations of " or ', $() or =$() or $(=) or =$(=) etc etc but just can't get it to work...  Have also tried using AsOfDate_DateFormat instead or putting a date(...,'YYYY-MM-DD') function within the modifier.

Also if I didn't bother with the AsOfDate_12m_Ago and bank holidays weren't an issue, I thought it should be something like this, but it gives null:

=sum({$<

  PricingBasis={"dealt"},

  AsOfDate_DateFormat = {$(#=addmonths(AsOfDate_DateFormat,-12))}

GBPValuation)

Help please!

Thanks very much,

Sarah

[Anil_Babu_Samineni]

Sarah, Try This

=sum({$<

  PricingBasis={"dealt"},

  AsOfDate_DateFormat = {'=$(addmonths(AsOfDate_DateFormat),-12)'} >}

GBPValuation)

Can you please let me know What you want to achieve?

[sarahallen1]

Hi Anil,

Thanks, unfortunately this doesn't work.  It gives 0. 

Also I need to account for the bank holidays so can't use the simple addmonths function, it needs firstworkdate and bank holiday variables too (which is why I put that in the script instead).

Thanks

[mikecrengland]

Create a text object and use =addmonths(AsOfDate,-12) as the text. I selected 2016-09-19 (AsOfDate) and the text object displayed 19/09/2015. Is that what you'd expect to see? If not, you might need to just adjust the formula a little.

[sarahallen1]

Hi Michael,

Yes that's what I get and what I'd expect.  The set analysis gives 0 because there is no valuation for 19/09/2015 because of the bank holidays.  So I need to use the field AsOfDate_12m_Ago instead which is calculated like this:

if(upper(Fund_PooledVal) like 'US*',                           FirstWorkDate(AddMonths(AsOfDate,-12),1,$(vUKHolidays),$(vUSHolidays),$(vIrHolidays)) //US and Ireland and UK
           , if(upper(Fund_PooledVal) like '*GLOBAL*OFFSHORE*',     FirstWorkDate(AddMonths(AsOfDate,-12),1,$(vUKHolidays),$(vUSHolidays),$(vIrHolidays))   //US and Ireland and UK
           , if(upper(Fund_PooledVal) like '*GLOBAL*',                   FirstWorkDate(AddMonths(AsOfDate,-12),1,$(vUKHolidays),$(vUSHolidays))                        //US and UK
           , if(upper(Fund_PooledVal) like '*OFFSHORE*',            FirstWorkDate(AddMonths(AsOfDate,-12),1,$(vUKHolidays),$(vIrHolidays))                        //Ireland and UK
           ,                                                                         FirstWorkDate(AddMonths(AsOfDate,-12),1,$(vUKHolidays))                                             //UK
           ))))  as AsOfDate_12m_Ago,

Thanks,

Sarah

[sarahallen1]

The variables are constructed in the script using an external source of bank holidays, they look like this:

'01/01/2003','17/03/2003','21/04/2003','05/05/2003','02/06/2003','04/08/2003','27/10/2003','25/12/2003',...,'26/10/2020','25/12/2020','26/12/2020'

[sarahallen1]

[bump...]

If anyone can help on this it would be much appreciated!  Still can't get it to work.

Thanks

[sarahallen1]

QlikView App Development‌;

[sunny_talwar]

It seems like your AsOfTable setup is not correct. I think you will need to rethink this, but before you do, I would suggest looking here The As-Of Table

I think you probably need to create flags for 12m, 3m, 1m data in addition to the dates itself. and the these new dates should join with your actual dates and the new date would be your master date which you would need to use in your chart objects.