Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
sarahallen1
Contributor II

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

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

8 Replies

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

Sarah, Try This

=sum({$<

  PricingBasis={"dealt"},

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

GBPValuation)


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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sarahallen1
Contributor II

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

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
Contributor III

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

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
Contributor II

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

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
Contributor II

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

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
Contributor II

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

[bump...]

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

Thanks

sarahallen1
Contributor II

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

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

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.