Hi Elad. I want to make sure I understand your question.
Is it that you want to simultaneously display
(1) active number of active deals during a week with a trend line
(2) the number of active deals for the end of a week by only showing the last day in the week?
Could you give us an idea what you would want the finished visual to look like?
i need the first option.
regarding the above example,
on the first day we have 1M, on second day we have additional 100K
on third day we have additional 200K
on last day 300K deals were closed so total number of active deals went down to 1M.
the sum of active deals for the whole period is therefor 1M.
One possible idea would be to use the lookup function (if the week trend is always over 4 or 5 days--here it's only 4, so my below example may need to be adjusted) to create a calculated field and use a dual axis with Number of Active Deals. For example, the calculation may look similar to the following:
if LOOKUP(sum([# of Active Deals]) , first())=LOOKUP(sum([# of Active Deals]) , first()+3) then LOOKUP(sum([# of Active Deals]) , first()+3)
else LOOKUP(sum([# of Active Deals]) , first()+3) -LOOKUP(sum([# of Active Deals]) , first())
Hope this helps a bit!
thank you but this is not what i need.
i know its a bit hard to understand but i'll try to explain again:
lets say that for a period of 30 days, each day i have 1M active deals.
if i want to present a trend on the week level, the graph will show 7M active deals per week which is not really the result.
i think it is similar to an inventory management data:
each day you have inventory going in and out of your store and you want to know every day what is your final stock level.
point is that in my case, i dont know how many items went in or out of the store, i only know the "end of day" sum.
now take that and group it on week level....