+ Reply to Thread
Results 1 to 5 of 5

Active sales promotions per week

  1. #1
    Registered User
    Join Date
    01-12-2019
    Location
    Herning, Denmark
    MS-Off Ver
    Office 2013
    Posts
    2

    Question Active sales promotions per week

    Hello dear Excellers!

    I'm posting here after hitting a wall with Excel for an awful amount of time, and despite my nature of not giving up easy, I had to post this thread.

    The problem

    I have an imported table which consists of information about sales promotions. What I'm currently looking into and most interested in is the number of weeks each promotion was valid (or active) and also which calendar weeks it is in.

    What I'm working with


    The way I use to calculate the duration of a sales promotion is fairly simple - I have the starting date and the finish date of the promotion in each row. (1 row = 1 promotion)

    The goal

    However, what makes things a bit more spicy and complicated is the fact that I want to be able to do a Pivot Chart (Clustered column or a Line chart) with every week of the year on the horizontal axis and the count of active promotions for this specific weeks, such as:

    image001.png

    I tried with creating some measures, as I guess the solution is most likely hidden somewhere in there, but as you can already guess, I had no success at all. My next attempt was trying to add multiple columns which list every week a promotion was active (some sales promotions last for over a year, so I added 57 columns as a safe margin) and then planned on including them as columns in a pivot chart, but I quickly discovered that the Pivot Chart has a limit of 255 line items (it stops adding columns after the 9th).

    I'll be super grateful if someone can give me an idea.

    Thank you for your time, guys!

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    2010 & 2016
    Posts
    4,698

    Re: Active sales promotions per week

    Hi and welcome,

    It would be easier to help if you could attach a copy of your workbook so we can see the data layout.

    BSB
    Please show appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,990

    Re: Active sales promotions per week

    Agree that a sample file will help a lot. However, have you looked at using countifS() to do the counting?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    01-12-2019
    Location
    Herning, Denmark
    MS-Off Ver
    Office 2013
    Posts
    2

    Re: Active sales promotions per week

    Hi again,

    Thank you for the quick replies! The complete dataset is fairly obfuscated with a lot of additional and unnecessary data, but here I tried to clean it and make it a bit more relevant for this specific purpose.

    FDibbins I thought about it, but the way I see this one working is by making an additional table, where all the individual week numbers will be listed in the first column, and the
    total count of active promotions per this week will be listed in the second column. This would work perfectly fine for generating the graph to some extent (I'm still not entirely sure how can I deal with the issue of a promotion expanding into the next year - then the weeknum of "Promotion End Date" will be lower thant the weeknum of "Promotion Start date" and I also would like to filter by year and etc), but the thing that concerns me about that approach is that I won't be able to list the individual promotions through clicking on specific week on the pivot table.

    I'd also like to apply some filtering, based on other parameters, such as "Company Code Description", "Creator Name" and etc.


    I would appreciate any sense of direction.
    Attached Images Attached Images

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,990

    Re: Active sales promotions per week

    We need to see some actual (sample) data, so we can play with it and see the best way to get you what you want

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1