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: 
gradiske
Contributor III
Contributor III

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
MK_QSL
MVP
MVP

Try

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

/

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

gradiske
Contributor III
Contributor III
Author

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

Michiel_QV_Fan
Specialist
Specialist

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
MVP
MVP

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
Contributor III
Contributor III
Author

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
Specialist
Specialist

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Specialist
Specialist

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.