Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

7 Replies
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

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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
Partner - Specialist III
Partner - Specialist III

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

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

Not applicable
Author

It works! Thanks!