StefanB
New member
- Joined
- Jan 13, 2019
- Messages
- 2
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 2013
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:
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!
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:
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!
