topic Re: last two years in a chart in QlikView

last two years in a chart

francis_gr — Wed, 25 Nov 2020 16:16:04 GMT

Hi!!

How can I show in a chart, by default, the last two years? (only in this chart) Year is a dimension and i can select the years in a

list box.

I appreciate any help

Regards

Re: last two years in a chart

swuehl — Sat, 15 Sep 2018 18:18:30 GMT

What do you mean with 'by default'? Should a selection in Year show the default or the selected years? Or any other condition?

Try maybe an expression in your chart like

=If(GetSelectedCount(YearField),

Sum(Value),

Sum({<YearField = {$(=Max(YearField)),$(=Max(YearField)-1)}>} Value)

)

The second Sum() uses Set Analysis to create a specific Year filter for the last two years.

Re: last two years in a chart

francis_gr — Sun, 16 Sep 2018 09:52:38 GMT

Hi Stefan!

Thanks for your reply.

Your expression is perfect and solves my question, but, abusing a little of your patience, I would need to know what would be the expression to show on the same chart, for the selected years , the average of "val" field por the people with the same "Pto" field

Sincerely thanks for your help.

Please, see my attached file

Re: last two years in a chart

swuehl — Sun, 16 Sep 2018 11:44:49 GMT

Maybe something like

=If(GetSelectedCount(Year),

Avg(

Aggr(

Avg(total<Year,Pto> Val)

, Year, Pto,Num)),

Avg({<Year = {$(=Max(Year)),$(=Max(Year)-1)}>}

Aggr(

Avg({<Year = {$(=Max(Year)),$(=Max(Year)-1)}>} total<Year,Pto> Val) // Count({<Year = {$(=Max(Year)),$(=Max(Year)-1)}>} DISTINCT Num)

, Year, Pto,Num))

)

But if you want to add this as another expression in your existing line chart with already existing two dimensions, this will not really work well, I think. But it should work in a table chart or if you use the expression as part of your line chart expression (for example as denominator to normalize your values per person).

Re: last two years in a chart

francis_gr — Sun, 16 Sep 2018 18:15:37 GMT

Ok Stefan! That’s pretty close.

Just need that for the year(s) selected, if I select a name , line chart shows the selected person and the average val of all persons with the same job (pto field).

If I select a “pto” it must show all persons with the same “pto” and the average “val” for all of them.

Thanks for your suday time.

Regards

Re: last two years in a chart

fema3773 — Tue, 03 Mar 2020 15:49:23 GMT

I got this to work perfectly except when I need to add a second condition. It shows no data to display. This is what I am trying to use:

=If(GetSelectedCount(Year),

Count({<[Cause (MIDAS)] = {'Communication failures'}>}[MIDAS Record ID]),

Count({<Year = {$(=Max(Year)),$(=Max(Year)-1)>} {<[Cause (MIDAS)] = {'Communication failures'}>[MIDAS Record ID]>))

Do you know how to fix so I can default to the past two years for only records that have a Cause[MIDAS] that = Communication Failure?