0 Replies Latest reply: Jan 18, 2017 10:16 AM by Brian Ferrell RSS

    Fiscal calendar with partial weeks

    Brian Ferrell

      Hi Qlik community!

       

      First of all, I have learned a ton from all of you and I am thankful for all of the great posts!

      Would be completely lost without these discussions/questions/answers.

       

      Time for my 1st question!

       

      Due to the sensitive nature of the dataset, I cannot share the QVW.

      I can, however, post the script.

       

      Table structure

      • Main data load (sales/cogs data)
      • Mapping files
      • Estimates (using key to join to main data table)
        • Estimates only have category(or system)/year/quarter level data and no other fields
      • Master Calendar (can't get this to work)
      • Inline load of workdays in Q1-Q4

       

      Problem:

       

      1) I want the calendar to show partial fiscal weeks by splitting weeks that overlap quarters.

        • Ex: For 2016, FW 13.1 is in Q1...and 13.2 is in Q2.
      • My calendar script is doing this...but adding one extra week to the end of each quarter. Since it is also including the "extra week" in the next quarter (where it should be)....it is double counting the last week of every quarter.
        • Ex: I select Q1 and see weeks 1.1 - 14.1 (Q1 ends week 13.1). Then Q2 goes from 13.2 - 27.1 (but should end at 26.1).


      2) We were not provided fiscal week level estimates, but would like to calculate them via the following logic:

      • Quarter Estimate * (# working days in week/# working days in quarter) = Fiscal Week Estimate
      • ....I can't seem to get this to work with my calendar

       

      Script:


      I have left out the tabs where I set filepaths and do mappings.

       

      See attached Word doc.

       

       

      Thank you, I look forward to learning and hearing your responses!!!