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

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

sarahallen1 — Wed, 21 Sep 2016 13:52:02 GMT

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:

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.
There is another set modifier for pricingbasis=dealt. Shouldn't cause issues?
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

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

Anil_Babu_Samineni — Wed, 21 Sep 2016 14:13:26 GMT

Sarah, Try This

=sum({$<

PricingBasis={"dealt"},

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

GBPValuation)

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

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

sarahallen1 — Wed, 21 Sep 2016 15:20:01 GMT

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

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

mikecrengland — Wed, 21 Sep 2016 15:27:34 GMT

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.

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

sarahallen1 — Thu, 22 Sep 2016 08:24:57 GMT

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

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

sarahallen1 — Thu, 22 Sep 2016 08:28:16 GMT

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'

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

sarahallen1 — Wed, 19 Oct 2016 12:48:14 GMT

[bump...]

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

Thanks

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

sarahallen1 — Wed, 19 Oct 2016 12:49:18 GMT

QlikView App Development‌;

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

sunny_talwar — Wed, 19 Oct 2016 19:56:59 GMT

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.