topic Re: Summing by Max Year in QlikView

Summing by Max Year

Tue, 04 Feb 2014 09:03:38 GMT

Hi All, new to click view and probably something really obvious using Set Analysis

When I use this formula

Sum({<Year={2014}>} RenewedCounter)

I get the required result, I.e. If I change a list box with Year in it, the graph always shows 2014

However when I try and make the calculation dynamic using a Max(Year) function it doesn't work as I expect:

Sum({<Year={$(=Max(Year))}>} RenewedCounter)

While the expression is valid, changing the list box changes the result to the maximum year selected. Ie if 2012 & 2013 are selected it shows 2013 results.

I want it to show 2014 no matter what year is selected

Re: Summing by Max Year

agilos_mla — Tue, 04 Feb 2014 09:09:29 GMT

You have to disregard the Year selections. Here are two ways

Sum({<Year={$(=Max({1}Year))}>} RenewedCounter) //disregarding all selections

Sum({<Year={$(=Max(Year)), Year=}>} RenewedCounter) // disregarding the current selection on Year Listbox.

Michael

Re: Summing by Max Year

nilesh_gangurde — Tue, 04 Feb 2014 09:10:55 GMT

Hi you have to add double quotes

try dis.

Sum({<Year={"$(=Max(Year))"}>} RenewedCounter)

Re: Summing by Max Year

Tue, 04 Feb 2014 09:10:59 GMT

Without selection you want to show max(year) i mean 2014 selection right

try like this

create one variable like this

Vmaxyear= max(year(yourdatefield))

and write setanalysis like this

Sum({<Yourdatefield={$(Vmaxyear)}>} RenewedCounter)

hope this will helps you

Re: Summing by Max Year

Tue, 04 Feb 2014 09:26:26 GMT

Hi,

Use this below piece of coding.

=Sum({1<Year = {$(=Max(Year))},Quarter = ,Month = >} RenewedCounter)

You have to replace $ to 1.

$ - Mean Current selection

1 - Mean Regardless of selection

I hope this will help you.

Thanks & Regards,

Raju

Re: Summing by Max Year

Tue, 04 Feb 2014 10:28:50 GMT

Thanks for this , it is very useful.

I am now trying to get another graph to show just the last 10 days of data

My Date field is ReportDate. The attached seems to bring through every day in 2014 and not just the last 10 days.

ideally I would like to remove the year part so when we are on the 2nd Jan 2015 it will show 8 days worth of December also.

sum({<Year={$(=Max({1}Year))},ReportDate={">=$(=ReportDate(Max(Reportdate)-10)<=$(=ReportDate(Max(ReportDate))))"}>}RenewedCounter)