Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe like attached
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)
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?
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)
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
Hello,
any idea about this issue?
Thanks.
--
Marco
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
Maybe like attached
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
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.