Solved: Re: Fix Values in Formula - Qlik Community

Anonymous · ‎2014-09-08

Hello Guys,

I need some help. Actually i have this formula in a chart bar :

=avg(aggr(count({<[Fabricante_Codigo]={'344'}, Data={">$(=Date(addMonths(max(Data),-12)))<=$(=Date(max(Data)))" },Segmento={'Naked Big'} >}[Numero_Chassis]), Mesano))

With this calculated dimension:

=if(Date(Data) >= Date(MonthStart(AddMonths(Today(), -12))), Mesano)

This chart shows me 12 bars, about last 12 months. But, if somebody select a previous month (July, for example), it shows only 1 bar.

I've tried to set '1' in the set analysis, but it didnt worked, nothing change. My field 'Mesano' is the concatenation of :

Month & '-' & Year

The aggr function is to set the correct order of months. If i only use the Count formula, the months stays out of order.

How can i solve this?

Thanks !

vgutkovsky · ‎2014-09-08

You definitely don't need a calculated dimension here. Go to the Sort tab of your chart and define your sort order there. The problem is that Mesano is a text concatenation instead of a true date. You can either fix that in the script (which is the correct way of doing it) or hack it in the Sort. If you want to do the latter, select "Expression" under Sort and set this as your expression:

date#(Mesano,'MMM-YYYY')

I'm guessing that's your date format, but if not just replace it with the correct format. The correct way of doing it of course is to make Mesano a true date field in the script.

Vlad

View solution in original post

Report Inappropriate Content · ‎2014-09-08

I am not sure but Can you try this Please?

=avg({1}aggr(count({<[Fabricante_Codigo]={'344'}, Data={">$(=Date(addMonths(max(Data),-12)))<=$(=Date(max(Data)))" },Segmento={'Naked Big'} >}[Numero_Chassis]), Mesano))

Thanks

Suraj

Anonymous · ‎2014-09-08

Hello Suraj,

Thanks for the answer!

It didnt worked, still shows 1 month.

Any other idea?

Thanks

vgutkovsky · ‎2014-09-08

I don't understand. You have an average per month, but your dimension is also month? Wouldn't that average just be whatever the count is for that month?

Vlad

Report Inappropriate Content · ‎2014-09-08

This one?

=avg({1}aggr(count({1<[Fabricante_Codigo]={'344'}, Data={">$(=Date(addMonths(max(Data),-12)))<=$(=Date(max(Data)))" },Segmento={'Naked Big'} >}[Numero_Chassis]), Mesano))

Anonymous · ‎2014-09-08

Hello Vlad,

Yes, you are correct. But, still it doesnt work. Any other idea?

Thank you,

Morandi

vgutkovsky · ‎2014-09-08

My goodness, that's a complicated way of doing a simple thing 🙂 Replace your dimension with just Mesano. Expression should be:

count({<Messano,[Fabricante_Codigo]={'344'}, Data={">$(=Date(addMonths(max(Data),-12)))<=$(=Date(max(Data)))" },Segmento={'Naked Big'} >}[Numero_Chassis])

Regards,

Vlad

Anonymous · ‎2014-09-08

Vlad,

Thank You, your answer was very helpfull.

The bar chart works, but it is not in order. I'm using this HUGE expression in sort tab, but it doest work with selection.

So, without selection, the bar chart is ok, but with selection, it is out of order. Can you help me?

Expression :

=if(Ano='2010' and Mes='jan', 101,

if(Ano='2010' and Mes='fev', 102,

if(Ano='2010' and Mes='mar', 103,

if(Ano='2010' and Mes='abr', 104,

if(Ano='2010' and Mes='mai', 105,

if(Ano='2010' and Mes='jun', 106,

if(Ano='2010' and Mes='jul', 107,

if(Ano='2010' and Mes='ago', 108,

if(Ano='2010' and Mes='set', 109,

if(Ano='2010' and Mes='out', 110,

if(Ano='2010' and Mes='nov', 111,

if(Ano='2010' and Mes='dez', 112,

if(Ano='2011' and Mes='jan', 201,

if(Ano='2011' and Mes='fev', 202,

if(Ano='2011' and Mes='mar', 203,

if(Ano='2011' and Mes='abr', 204,

if(Ano='2011' and Mes='mai', 205,

if(Ano='2011' and Mes='jun', 206,

if(Ano='2011' and Mes='jul', 207,

if(Ano='2011' and Mes='ago', 208,

if(Ano='2011' and Mes='set', 209,

if(Ano='2011' and Mes='out', 210,

if(Ano='2011' and Mes='nov', 211,

if(Ano='2011' and Mes='dez', 212,

if(Ano='2012' and Mes='jan', 301,

if(Ano='2012' and Mes='fev', 302,

if(Ano='2012' and Mes='mar', 303,

if(Ano='2012' and Mes='abr', 304,

if(Ano='2012' and Mes='mai', 305,

if(Ano='2012' and Mes='jun', 306,

if(Ano='2012' and Mes='jul', 307,

if(Ano='2012' and Mes='ago', 308,

if(Ano='2012' and Mes='set', 309,

if(Ano='2012' and Mes='out', 310,

if(Ano='2012' and Mes='nov', 311,

if(Ano='2012' and Mes='dez', 312,

if(Ano='2013' and Mes='jan', 401,

if(Ano='2013' and Mes='fev', 402,

if(Ano='2013' and Mes='mar', 403,

if(Ano='2013' and Mes='abr', 404,

if(Ano='2013' and Mes='mai', 405,

if(Ano='2013' and Mes='jun', 406,

if(Ano='2013' and Mes='jul', 407,

if(Ano='2013' and Mes='ago', 408,

if(Ano='2013' and Mes='set', 409,

if(Ano='2013' and Mes='out', 410,

if(Ano='2013' and Mes='nov', 411,

if(Ano='2013' and Mes='dez', 412,

if(Ano='2014' and Mes='jan', 501,

if(Ano='2014' and Mes='fev', 502,

if(Ano='2014' and Mes='mar', 503,

if(Ano='2014' and Mes='abr', 504,

if(Ano='2014' and Mes='mai', 505,

if(Ano='2014' and Mes='jun', 506,

if(Ano='2014' and Mes='jul', 507,

if(Ano='2014' and Mes='ago', 508,

if(Ano='2014' and Mes='set', 509,

if(Ano='2014' and Mes='out', 510,

if(Ano='2014' and Mes='nov', 511,

if(Ano='2014' and Mes='dez', 512,

)))))))))))) )))))))))))) )))))))))))) )))))))))))) ))))))))))))

Thank You,

Morandi

Anonymous · ‎2014-09-08

Hello Suraj,

Your expression works, but only with the calculated dimension.

But my main problem, is the order of months. Your expression works, but doesnt work selection, it shows the bars out of order.

Thanks,

Morandi

Report Inappropriate Content · ‎2014-09-08

Hey Wagner,

Try Vlad expression with 1 infront of it. Like below

count({1<Messano,[Fabricante_Codigo]={'344'}, Data={">$(=Date(addMonths(max(Data),-12)))<=$(=Date(max(Data)))" },Segmento={'Naked Big'} >}[Numero_Chassis])