Previous date after a selection

[#1]

Hello,

Actually, I try to make a display two tables. First one with the current selection and the second previous quarter. These tables come from 1 Excel.

Quarter:

Quarter
01/07/2013
01/10/2013
01/01/2014
01/04/2014

Current Selection:

Quarter	Information 1	Information 2	Information 3
01/10/2013	AAAAA	AAAAA	AAAAA
01/10/2013	BBBBB	BBBBB	BBBBB

Previous Quarter:

Quarter	Information 1	Information 2	Information3
01/07/2013	CCCCC	CCCCC	CCCCC
01/07/2013	DDDDD	DDDDD	DDDDD
01/07/2013	EEEEE	EEEEE	EEEEE

Can I make the second table with table box or it need something more specific in the script?

Regards

[marcus_sommer]

You need to use a straight-table with set analysis expressions for the second table to be independent from the selection, something like this: only({< Quarter = {"$(=max(Quarter)-1)}>} [Information 1])

- Marcus

[#3]

Thanks for your help.

I don't really understand this expression. I know as you said to put my second table independent, but should I use it in "Expression" from my straight-table? because doing this I only have a new column.

Thanks again.

Regards

[gsbeaton]

There's no elegant way of doing what you want to do in a table box, a straight table with some set analysis is your best bet.  Have a look at this post by Richard Pearce: Calendar with flags making set analysis so very simple.  It documents a very easy way of achieving what you are trying to do by setting flags in your calendar load script.  Getting the previous quarter becomes as simple as sum({<Previous_Quarter={1}>} [Information 1]).

[miguelbraga]

Hi Marcus,

A little change in answer:

For the column of Information 1 in the Previous Quarter Table:

=only({< Quarter = {"$(=max(Quarter)-1)"}>} [Information 1])

For the column of Information 2 in the Previous Quarter Table:

=only({< Quarter = {"$(=max(Quarter)-1)"}>} [Information 2])

For the column of Information 3 in the Previous Quarter Table:

=only({< Quarter = {"$(=max(Quarter)-1)"}>} [Information 3])

For the column of Information 4 in the Previous Quarter Table:

=only({< Quarter = {"$(=max(Quarter)-1)"}>} [Information 4])

The Current Selection Table can be a Table Box

In Straight Table, please disable the totals count...

Regards,

MB

[marcus_sommer · Accepted solution]

Yes, you need to use these set analysis expressions instead of the most dimensions, like:

Dim1: Quarter

Expr1: only({< Quarter = {"$(=max(Quarter)-1)}>} [Information 1])

Expr2: only({< Quarter = {"$(=max(Quarter)-1)}>} [Information 2])

...

and Quarter needs to be numeric (in your screenshot it's left-aligned like a string!) to be able to calculate the previous Quarter and because your Quarter seems to be a date it will be probably look like:

only({< Quarter = {"$(=addmonths(Quarter, -3))}>} [Information 1])

- Marcus

[marcus_sommer]

Of course, you are right - the ending double quote is missing:

only({< Quarter = {"$(=addmonths(Quarter, -3))"}>} [Information 1])

[#8]

It works! Thanks!