topic Re: Year vs. year prior help in QlikView

Year vs. year prior help

Anonymous — Fri, 26 Sep 2014 09:57:25 GMT

HI,

I have what I hope is a simple fix involving set analysis which. I can't get to work properly. I have data broken down by month (numerical) and years. I am having two issues when trying to create a bar graph.

1. The data is plotting as a summation of the year ie continually going up verus just a month to date number. Any thoughts?

2. Given the way the date is represented, which is the best way for my to plot year vs year prior data, and then be able to break down to month versus month year prior.

THank you for the help in advance!

Re: Year vs. year prior help

jyothish8807 — Fri, 26 Sep 2014 10:14:38 GMT

Hi Justin,

can you share the app or screenshot? That will be helpful to understand the requirment.

Regards

Re: Year vs. year prior help

Anonymous — Fri, 26 Sep 2014 12:27:59 GMT

KC,

Here are a couple of pictures of my file. I am looking to sum the premium data but only showing for the month To date. Also, looking to view year over year.

Re: Re: Year vs. year prior help

jyothish8807 — Fri, 26 Sep 2014 15:12:07 GMT

Hi Justin,

try this may be helpful.

Regards

Re: Year vs. year prior help

ToniKautto — Fri, 26 Sep 2014 18:46:42 GMT

I moved your question to the QlikView development forum, based on your QlikView screenshots. The question was originally posted in the Qlik Sense forum.

Re: Year vs. year prior help

Anonymous — Fri, 26 Sep 2014 20:25:25 GMT

KC,

Thank you for the help. I have the personal edition currently till work upgrades. Any type of work through?

Re: Year vs. year prior help

jyothish8807 — Sat, 27 Sep 2014 06:22:21 GMT

Hi justin,

Create a new field in script:

Rowno() & Month as New_Month

Take this field as Dimension

And in expression take:

SumofEran PREM

Hope it will work

Re: Year vs. year prior help

Anonymous — Mon, 06 Oct 2014 13:55:20 GMT

KC,

That didn't seem to work as I need to have the month name still in there and the expression above gave me over 5000 combinations. It seems like a function similar to could work:

If(RowNo() = 1, [SumofNetWritPrem], [SumofNetWritPrem]-Peek([SumofNetWritPrem],-1))

I just can't seem to get the syntax to work. In my data set there are 5 years of data. The data works properly in month one, but each month subsequent is not working proper. Any thoughts?

Re: Year vs. year prior help

ngulliver — Mon, 06 Oct 2014 15:19:38 GMT

Hi, Justin.

Have you seen Steve's post on this ? It may give you some insight into how to build your prior year comparison.

QlikView App: Set Analysis - Prior Period Comparison

Cheers,

Neil

Re: Year vs. year prior help

Anonymous — Mon, 06 Oct 2014 15:39:41 GMT

Neil,

Thank you for the help. I tried that method, but I couldn't get it to work. Since I am on Personal edition for now, I couldn't include his work. My data includes Month and Year and not date. How should I append the data to properly use the variable including date.

The problem with my data seems to be as follows. There are 30,000+ lines of data, and when I graph the sum of the field, it brings up aggregate data. I have tried several methods but ultimately I seem to be missing the correct method. I ultimately need month 1 which is showing correct data per each year, then I need the difference for each other month, such as the difference between (FEB and JAN) or (MAR and FEB) and so on. I assume I need some sort of IF statement or set analysis but I am unsure of the syntax.

Re: Year vs. year prior help

jyothish8807 — Mon, 06 Oct 2014 16:24:43 GMT

Hi Justin,

Do you want all Aug 2014 data as a single entry or as multiple entries as mentioned in your sheet?

Regards

Re: Year vs. year prior help

Anonymous — Mon, 06 Oct 2014 17:43:01 GMT

Hi KC,

My data is the accrual of premiums for entire months over multiple entries. When I use the sum of those entries by month in a bar graph it has a basic look as follows:

Jan - $20

Feb - $45

Mar - $67

Apr - $98

On my graph, the sum for Jan is correct. However, the sum for every other month in the year is incorrect. What I would actually look for in the other months is the difference, such as $25 for Feb, and $22 for Mar

Justin

Re: Year vs. year prior help

simenkg — Mon, 06 Oct 2014 17:47:14 GMT

You keep Month as the dimension.

Then you have two expressions:

Current Year:

Sum({<Year={"$(=max(Year))"}>}Amount)

Previous Year:
Sum({<Year={"$(=max(Year)-1)"}>}Amount)

In order to get the accumulated sum; on the expression tab check the "Full Accumulation" checkbox

Re: Year vs. year prior help

Anonymous — Mon, 06 Oct 2014 18:01:15 GMT

Simen,

Thank you for the help. I had tried that prior and ran in to the following problems.

1. This data includes 4 years of data, and is also used against a set plan figure. I can only get the last two years to show up on this file even if the users select 2012 or 2011 for instance.

2. Also in this graph, I am trying to create where users can use a drill down method. So in the months, the data is correct for January, but every other month is "corrupted" as it includes the prior months data. I am sure I need some sort of if statement or further way for users to be able to drill down.

A sort of : if (month = Jan, show month's sum of premium, show sum of current month - sum of month(-1).

Any thoughts on a way to either manipulate the data to add a new field, or a way to create an expression that will have a fix to cover all points?

Justin