Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
MVP & Luminary
MVP & Luminary

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

7 Replies
MVP & Luminary
MVP & Luminary

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

Not applicable

Re: Previous date after a selection

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
Contributor II

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

miguelbraga
Valued Contributor III

Re: Previous date after a selection

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

MVP & Luminary
MVP & Luminary

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

MVP & Luminary
MVP & Luminary

Re: Previous date after a selection

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

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

Not applicable

Re: Previous date after a selection

It works! Thanks!