topic Re: Fix Values in Formula in QlikView

Fix Values in Formula

Anonymous — Mon, 08 Sep 2014 17:33:10 GMT

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 !

Re: Fix Values in Formula

Mon, 08 Sep 2014 17:38:32 GMT

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

Re: Fix Values in Formula

Anonymous — Mon, 08 Sep 2014 17:41:17 GMT

Hello Suraj,

Thanks for the answer!

It didnt worked, still shows 1 month.

Any other idea?

Thanks

Re: Fix Values in Formula

vgutkovsky — Mon, 08 Sep 2014 17:44:14 GMT

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

Re: Fix Values in Formula

Mon, 08 Sep 2014 17:45:37 GMT

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

Re: Fix Values in Formula

Anonymous — Mon, 08 Sep 2014 17:46:48 GMT

Hello Vlad,

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

Thank you,

Morandi

Re: Fix Values in Formula

vgutkovsky — Mon, 08 Sep 2014 17:49:23 GMT

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

Re: Fix Values in Formula

Anonymous — Mon, 08 Sep 2014 17:57:09 GMT

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

Re: Fix Values in Formula

Anonymous — Mon, 08 Sep 2014 17:59:57 GMT

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

Re: Fix Values in Formula

Mon, 08 Sep 2014 18:01:43 GMT

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])

Re: Fix Values in Formula

Anonymous — Mon, 08 Sep 2014 18:04:35 GMT

Suraj,

Yes, i've inserted the '1' in the expression, thank you for the observation.

Now, this 2 formulas works, but selecting a value, none order correctly.

How can we solve this?

Thank you,

Morandi

Re: Fix Values in Formula

vgutkovsky — Mon, 08 Sep 2014 18:06:00 GMT

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

Re: Fix Values in Formula

Mon, 08 Sep 2014 18:06:42 GMT

what is the order you are looking for? Write in an expression and paste it in the sort tab expression dialog and remove all selections in the Sort tab.

Thanks

Suraj

Re: Fix Values in Formula

vgutkovsky — Mon, 08 Sep 2014 18:07:05 GMT

Suraj, the {1} set is not the correct way of doing things here. We don't typically want to ignore all QV selections. That's not a real solution.

Vlad

Re: Fix Values in Formula

Anonymous — Mon, 08 Sep 2014 18:08:51 GMT

Hello Vlad,

Yes, you correct, but i didnt mentioned that this is a chart for a report, which the only thing that i need to change is the Data.

But, you are correct !

Thank you !

Re: Fix Values in Formula

Mon, 08 Sep 2014 18:09:44 GMT

Oh k. if it should not change with Mesano, then it should be like this

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

Thanks

Suraj

Re: Fix Values in Formula

Anonymous — Mon, 08 Sep 2014 18:12:50 GMT

Hello Vlad,

Sorry, it worked without selection, with selection, it stays out of order.

Do you think that in script is the only way?

Thanks!

Re: Fix Values in Formula

vgutkovsky — Mon, 08 Sep 2014 18:15:46 GMT

No, it's not the only way--the Sort tab method should work just fine, regardless of selections. You've removed the calculated dimension, right?

Vlad

Re: Fix Values in Formula

Anonymous — Tue, 09 Sep 2014 14:11:02 GMT

Hello Suraj,

What i mean is this:

If its nothing selected, the sort stays correct.

If i select a previous month, it stays out of order.

Here goes a example:

Here, i dont have nothing selected.

Here i selected July.

As you can see, the year 2013 and 2014 is in a random sort.

My expression in sort tab is this (working and fine):

date#(Mesano,'MMM-YY')

Any idea?

Thanks,

Morandi

Re: Fix Values in Formula

vgutkovsky — Tue, 09 Sep 2014 14:42:59 GMT

There must be something unusual going on that's specific to your app. If you need more help, post a reduced example.

Regards,

Vlad