Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
ThHi all, in this scenario, I'd like to compare projects that I won or lost on each brand, the table bellow, Compares 2 dates 2017/07/07 on the left and 2017/07/08 on the righ, the counts depend on the project status that can be active or not, if it was active on the 7th and not active on the 8th it means the it was lost (ex project 265), otherwise like in project 268 Its a new project, if the value is active on the first date and second date then nothing happened and no counters are incremeted.
representada | idtbl_projectos | 20170707 | Lost | New | 20170708 |
C33 MODUL | 238 | 1 | 0 | 0 | 1 |
C33 MODUL | 265 | 1 | -1 | 0 | 0 |
C33 MODUL | 268 | 0 | 0 | 1 | 1 |
for column 20170707 imusing the expression:
>>Count(distinct{<SituacaoActiva={1},dtimport={$(dtcompA)}>} idtbl_projectos )
for column 20170708
>>Count(distinct{<SituacaoActiva={1},dtimport={$(dtcompB)}>} idtbl_projectos )
dtcompA and dtcompB are variables devined in inputs
For the Lost column the expression is :
if(Count(distinct{<SituacaoActiva={1}, dtimport={$(dtcompA)}>} idtbl_projectos)>0 and Count(distinct{<SituacaoActiva={1}, dtimport={$(dtcompB)}>} idtbl_projectos)=0,
count(DISTINCT{<SituacaoActiva={1}, dtimport={$(dtcompA)}>} idtbl_projectos),//-Count(DISTINCT{<SituacaoActiva={0}, dtimport={$(dtcompB)}>} idtbl_projectos),
0)
and for the New column
if(Count(distinct{<SituacaoActiva={1}, dtimport={$(dtcompA)}>} idtbl_projectos)=0 and Count(distinct{<SituacaoActiva={1}, dtimport={$(dtcompB)}>} idtbl_projectos)>0,
count(DISTINCT{<SituacaoActiva={1}, dtimport={$(dtcompB)}>} idtbl_projectos),//-Count(DISTINCT{<SituacaoActiva={0}, dtimport={$(dtcompA)}>} idtbl_projectos),
0)
This approach works when the table is expanded, but fails when I colapse it like below :
representada | 20170707 | Lost | New | 20170708 |
C33 SMURF | 2 | 0 | 0 | 2 |
This fails because I compare the totals on the dates columns and dispite being equal there were one lost project and one new
the result id like to get would be :
representada | 20170707 | Baixas | Altas | 20170708 |
C33 SMURF | 2 | -1 | 1 | 2 |
Thanks in advance
Jorge
Hi Jorge, If the expression needs to be calculated by row you can use aggr with alll dimensions of the table, and something to do with all the row values, in example using Sum()::
Sum(Aggr(Expression, representada, idtbl_projectos))
Hi Jorge, If the expression needs to be calculated by row you can use aggr with alll dimensions of the table, and something to do with all the row values, in example using Sum()::
Sum(Aggr(Expression, representada, idtbl_projectos))
Thanks. Worked as a charm !