Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gradiske
New Contributor

Expression considering month and month-1 with bar chart?

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 ;/)

9 Replies

Re: Expression considering month and month-1 with bar chart?

Try

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

/

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

gradiske
New Contributor

Re: Expression considering month and month-1 with bar chart?

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

michielvandegoo
Valued Contributor

Re: Expression considering month and month-1 with bar chart?

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%')

Re: Expression considering month and month-1 with bar chart?

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

Re: Expression considering month and month-1 with bar chart?

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 =/

michielvandegoo
Valued Contributor

Re: Expression considering month and month-1 with bar chart?

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

Re: Expression considering month and month-1 with bar chart?

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

Re: Expression considering month and month-1 with bar chart?

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

michielvandegoo
Valued Contributor

Re: Expression considering month and month-1 with bar chart?

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.

Community Browser