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

Extract values depending on periods condition

Hello,

in attached sample file I would create a new list box element containing those combinations of AGENTE and CLIENTE pairs that don't have Periods value in its PERIODO_0 field.

For instance, about first pair "1, 100076" I would like to see as a final result:

1     100076     2011-01

1     100076     2011-04

1     100076     2011-08

1     100076     2011-10

1     100076     2011-11

1     100076     2011-12

1     100076     2012-02

1     100076     2012-07

1     100076     2012-08

...

Thank you in advance and best regards.

--

Marco Pikiri

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like attached

View solution in original post

10 Replies
swuehl
MVP
MVP

I think you first need to get the two fields format to be the same, currently PERIODO_0 is text only, PERIODO shows dual values.

I did this (not having your source files) with a partial reload:

AgentiClienti00:

ADD LOAD

  ID_AZIENDA_0,

  CLIENTE_0,

  M_AGENTE_1_KEY,

  Date#(PERIODO_0,'YYYY-MM') as PERIODO_0,

  1 as Dummy

Resident AgentiClienti0;

DROP TABLE AgentiClienti0;



Now you can create a list box with a field expression like

=Aggr(Only({<Periodo -= p(PERIODO_0) >}Periodo),Periodo)

Not applicable
Author

Hi swuehl,

thank you very much for your support.

As conclusion of all I'd like now to show results on a grid element showing data like in my first post (1, 100076, 2011-01, ...).

I've spent all day, but didn't reach desired result... Only thing I have correct just when a single combination of AGENTE and CLIENTE is selected.

I'm now a bit confused too, I can't understand which could be the right object to use (pivot table? straight table?) or maybe I should also think about reanalyze data model?

swuehl
MVP
MVP

Set analysis is evaluated once per chart, not per your dimension lines.

So if you expect the p() function to consider your current dimension values, this won't work.

But if you don't expect that, you can create a straight table with three dimensions, Agente, Cliente, Periodo and as hidden expression

=Only({<Periodo -= p(PERIODO_0) >}Periodo)

Not applicable
Author

Swuehl,

if you try now to unselect CLIENTE 100076 I see the behavior I was mentioning: Periodo values disappear, just the common values between two CLIENTEs remain available and this is not what I want.

In other words, unselecting 100076 then original lines should stay there and I expect that new lines for other CLIENTE show up.

Regards.

--

Marco

Not applicable
Author

Hello,

any idea about this issue?

Thanks.

--

Marco

Not applicable
Author

Hello,

I'm sorry if I insist again, but I would need to know if desired behaviour could be obtained or not.

Thank you and best regards.

--

Marco Pikiri

swuehl
MVP
MVP

Maybe like attached

Not applicable
Author

Unbelievable. That's the final result I was expecting, thank you again.

But if you have other 5 mins of availability could you explain me in short your formula:

If(Sum( Aggr(Periodo = PERIODO_0, M_AGENTE_1_KEY, CLIENTE_0,Periodo, PERIODO_0))=0, 1,0) ?

Best regards.

--

Marco Pikiri

swuehl
MVP
MVP

The advanced aggregation function is building a virtual table, using M_AGENTE_1_KEY, CLIENTE_0,Periodo, PERIODO_0 as dimensions and

=Periodo = PERIODO_0

as expression. Since Periodo, PERIODO_0 are in different tables, all combinations of possible field values are created (cartesian product).

The expression is a comparison of the dimensional values and returns -1 if there is a match and 0 if not.

Then I aggregate / sum this expression column projecting the virtual table to the outer chart dimensions.

If there is no PERIODO_0 for a chart dimension value Periodo, 1 is returned, else 0, which can be used to hide the chart lines we don't want to see.