Showing Sum of Yesterdays Amount in Chart where Date is a Dimension

[nathanfurby]

Hello - feeling lucky today so maybe someone can help me out [:D]

I have been trying to resolve an issue and have searched high and low on the forums for help with no success. I am trying to show day on day moves in a chart - i.e. difference between what the Amount was yesterday and what it is today. Ulimately I would like to also show this in bar chart format.

I have this working in another application where date/previous date run of the latest selection and where the date field is not in the chart dimensions.

Please take a look at the simple sample I have created. Two problems:

1. How can I get Yesterday's Amount to show on Today's row where date is a dimension?
2. How can I get the previous date to calculate on every row in the table - i.e. without having to select just one date.

Please let me know if I am not being clear. Many thanks

[nathanfurby]

OK - maybe not as lucky as I thought. Bumping this again to see if anyone can help?

Thanks

[johnw · Accepted solution]

I didn't look closely as your example, but it sounds exactly like an example I posted yesterday which calculated, for each month, the growth from the same month in the previous year. It handles it with an extra table that connects and AsOfMonth to both the current month and the the same month in the previous year, with flags indicating what the connection means. Then I just use set analysis to grab the right connection. Your case would be similar, just with an AsOfDate connecting to both the current date and the previous date with flags. See attached.

[nathanfurby]

Thanks John. Looks very nice, will investigate how I can use it in my app soon.

[nathanfurby]

Thanks John. This does indeed give me the solution I was looking for. I have attached my modified example for future reference.

I was however wondering if you could tell me how you might practically implement this in a real application.

For example - the actual 'Date' field is one that would get selected/filtered by the user. I have therefore added an exclusion to any Date selections for the chart. If the user only wanted to see a specific date in the chart then they could select the 'AsOfDate' (which is the dimension in the chart). However, this would automatically filter on the two related dates throughout the rest of the application.

I suppose this is something the users could get used to or I could add an exclusion for any selections on 'AsOfDate' in the other charts in the application? I imagine this would cause confusion to the users especially when a 'Date' selection was made which was outside of the possible values of the 'AsOfDate' field - essentially clearing the 'AsOfDate' selection.

Am I making sense? Am I missing something obvious in terms of the best way to implement?

Many thanks if you have time to discuss.