Here I am coming with real
time challenge. Those who are interested can try this and test your knowledge.
Sample Data looks like below:
In above sample data we have
Items, Sub Items, Hour and Sales Count.
We have 7 Items (Item 1,
Item 2, Item 3, Item 4, Item 5, Item 6 and Item 7), 4 Sub Items for each Item and
we have Sales count by Hours, means how many items sold in that hour. For
example 0 hour means how many Items/Sub Items sold between 12 Am to 1 Am.
Using this data we have to build 3 reports:
Report 1:
This is simple one. Look like
below.
Report 2:
This is intermediate level. You need to do Bin Analysis.
Bins are like : 0 – 20, 20 –
40, 40 – 60, 60 – 80, 80 – 100, 100 – 150, 150 – 200 and 200 – 500
In
above image for 0 hours, 0 – 20 bin size is 6. How we got 6? Look at below
image
Report 3:
This is Very Complex level.
Above
report we are calculating Higher Frequency of the Hour and Max Sale Count
(Hourly)
Max Sale Count (Hourly):
Second Row in above Report
This
is simple we are just calculating Max value of Sale Count for each hour.
For
example let’s see first report image. We have sale count for 0 to 23 hours by
Item and Sub Item.
In
that we need to calculate Max value for each Hour Column.
So
we will get values 123, 133, 176 ……70 with respect to 0, 1, 2, 3 ……23 hours.
Still
if you have confusion at below image:
Higher Frequency of the
Hour: First Row in Report 3
This
is very complex to calculate. This needs expert level knowledge.
To know
how to calculate please look at below image:
Final output of 3rd
report should look like this:
For dataset please download
below workbook:
For more challenges: Click Here