7 Replies Latest reply: Oct 29, 2015 8:08 AM by George Beaton

# Previous date after a selection

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:

QuarterInformation 1Information 2Information 3
01/10/2013AAAAAAAAAAAAAAA
01/10/2013BBBBBBBBBBBBBBB

Previous Quarter:

QuarterInformation 1Information 2Information3
01/07/2013CCCCCCCCCCCCCCC
01/07/2013DDDDDDDDDDDDDDD
01/07/2013EEEEEEEEEEEEEEE

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

Regards

• ###### Re: Previous date after a selection

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

• ###### Re: Previous date after a selection

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

• ###### Re: Previous date after a selection

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

• ###### Re: Previous date after a selection

It works! Thanks!

• ###### Re: Previous date after a selection

Hi Marcus,

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

• ###### Re: Previous date after a selection

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

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

• ###### Re: Previous date after a selection

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]).