Expression considering month and month-1 with bar chart?

[gradiske]

Sup guys.

I'm trying to make a bar chart with a month dimension, but in my expression i have to divide by month-1 to get a percentage.

Dimension: [Mês Lotacao]

=num(count({$<FlagDemitidos={'S'}>} distinct CHV_CONTRATO)
/
count({$<[Mês Lotacao]={'$(=[Mês Lotacao]-1)'},FlagEstagiario={'N'}>} CPF_LOT2),'#.##0,00%')

I've tried a lot of variations, with addmonths and etc, but nothing seems to work.

Since the first row will consider the month on dimension, the second one should be the current Month in the chart-1.

Any ideas? i've only managed to get this thing done with 12 expressions(one per month ;/)

[MK_QSL]

Try

=num(count({$<FlagDemitidos={'S'}>} distinct CHV_CONTRATO)

/

count({$<[Mês Lotacao]={'$(=Max([Mês Lotacao])-1)'},FlagEstagiario={'N'}>} CPF_LOT2),'#.##0,00%')

[gradiske]

it kinda works but only returns month 11... i want to show every month possible in the current year.

[Michiel_QV_Fan]

Try the above function.

Make sure you chart is Sorted by Month.

=num(count({$<FlagDemitidos={'S'}>} distinct CHV_CONTRATO)

/

above(count({$<FlagEstagiario={'N'}>} CPF_LOT2)),'#.##0,00%')

[MK_QSL]

Set Analysis will take only one Month into consideration.. i.e. Only one month for whole table... You can't have different month for different line in one table...

[gradiske]

Sir, that works like a charm!

BUT, how can i handle this with Month 1(JAN)? Since i have to consider month 12 from last year... Any workaround with this implementation only for month 1?

EDIT: If i select 1 month, the graphic doesn't work =/

[Michiel_QV_Fan]

You might get that to work with a combination of if statements and set analysis

Untested:

=num(count({$<FlagDemitidos={'S'}>} distinct CHV_CONTRATO)

/

if(month = 1,

count({$<$<[Mês Lotacao]={'$(=Max([Mês Lotacao])-1)'},FlagEstagiario={'N'}>} CPF_LOT2),

above(count({$<FlagEstagiario={'N'}>} CPF_LOT2))

,'#.##0,00%')

edit:

of course with a selection there is no previous (above) value

[gradiske]

I've tried some if statements, but here's the problem with that.

I can say that Month 1 - 1 is 12, ok... but then i have to consider last year, so now im having the same problem i was having with the months to get Year-1.

[gradiske]

Anyone?

I was trying to solve the problem with month 01 with ABOVE function but i think that ABOVE will not work properly, i mean, without selecting months works OK, but when i select, for exemple, 5 months, it only shows 4 months, like that's nothing ABOVE the first one select, then will not show it.

I'm trying everything! =O

[Michiel_QV_Fan]

I think you better solve this in the script with the previous function.

Order your fact table in the right order and add a column by using previous(CPF_LOT2) as <your name>.

This will bring the value CPF_LOT2 for the previous period on the same line as te current CHV_CONTRATO value.

You can also do the calculation in the script.

Please review the help text.