Gareth Horton
July 12th, 2002, 02:26 PM
Michael Jul Hansen's post is correct in that you will get the following results in the Summary
window if you follow his instructions:
Month 01Jan2002 01Feb2002
Dec2002 US 200 US 150
In this suggested solution, however, the Variance column would have to be added in Excel. Not
a big problem, of course, but we are looking into including more options in the Summary to
allow for specifying additional columns that do simple arithmetic such as Column A minus Column B, etc.
Cyril, does this solution work for you? It actually is possible to get the view of data you are
looking for, including the Variance column, completely using Monarch, but it is more complex than
Michael Jul Hansen's practical solution. Please let me know.
Michael Jul Hansen wrote:
> Easy..... just make a summary where your key fields are month and report date and summary field is amount - make sure that report date is displayed ACROSS, and you will be fine - the sum of amount column can just be discarded when you have exported to excel
>
> Cyril Wong wrote:
> > This query relates to accounting principles, where, if using monarch I can extract the following
> >
> > Report Date Month Amount
> > 01Jan2002 Dec2002 US 200
> > 01Feb2002 Dec2002 US 150
> >
> > So far quite easy (standard monarch template).
> >
> > But I wanted to make a calculation field that shows the variance for Dec, based on 2 different report dates. How do I do that one? That in the end allows me to export to MS Excel and show me in the MS Excel Pivot table format as follows
> >
> > Report Date
> > Month 01Jan2002 01Feb2002 Variance
> > Dec2002 US 200 US 150 US 50
> >
> > Can anybody help?
> >
> >
>
window if you follow his instructions:
Month 01Jan2002 01Feb2002
Dec2002 US 200 US 150
In this suggested solution, however, the Variance column would have to be added in Excel. Not
a big problem, of course, but we are looking into including more options in the Summary to
allow for specifying additional columns that do simple arithmetic such as Column A minus Column B, etc.
Cyril, does this solution work for you? It actually is possible to get the view of data you are
looking for, including the Variance column, completely using Monarch, but it is more complex than
Michael Jul Hansen's practical solution. Please let me know.
Michael Jul Hansen wrote:
> Easy..... just make a summary where your key fields are month and report date and summary field is amount - make sure that report date is displayed ACROSS, and you will be fine - the sum of amount column can just be discarded when you have exported to excel
>
> Cyril Wong wrote:
> > This query relates to accounting principles, where, if using monarch I can extract the following
> >
> > Report Date Month Amount
> > 01Jan2002 Dec2002 US 200
> > 01Feb2002 Dec2002 US 150
> >
> > So far quite easy (standard monarch template).
> >
> > But I wanted to make a calculation field that shows the variance for Dec, based on 2 different report dates. How do I do that one? That in the end allows me to export to MS Excel and show me in the MS Excel Pivot table format as follows
> >
> > Report Date
> > Month 01Jan2002 01Feb2002 Variance
> > Dec2002 US 200 US 150 US 50
> >
> > Can anybody help?
> >
> >
>