topic Re: Dealing with change over time function in QlikView

Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 18:41:15 GMT

Hello,

I was able to create a master measure item to compute the period over period growth, but the problem is when I try to show the most current growth value, I need to select both dates otherwise the value is null. I can understand why this is happening, but is there a way around this?

Thank you,

Jessica

Re: Dealing with change over time function

sunny_talwar — Fri, 26 Jun 2015 18:43:36 GMT

There is a way to do it but you would need to share a sample. Do you have a sample you can share?

Best,

Sunny

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 18:50:49 GMT

I would like to ultimately compute the growth rate for each of these indices and represent the most recent change in the most recent time period. For instance.

Original series:

2011 - 100

2012 - 140

2013 - 100

2014 - 50

2015 - 100

New series:

2012 - 40%

2013 - 29%

2014 - 50%

2015 - 100%

This is my original load statement -

COLI:

LOAD

"Urban Area" as MSA,

STATE,

"100% Composite Index",

"13 % Grocery Items",

"29 % Housing",

"10% Utilities",

"12 % Transportation",

"4% Health Care",

"32 % Miscalaneous Goods and Services"

FROM [lib://Cost of Living/Cost of Living Index_Quarterly.xlsx]

(ooxml, embedded labels, table is [Qlik Load]);

I hope this helps.

Thank you!

Re: Dealing with change over time function

Anonymous — Fri, 26 Jun 2015 18:51:15 GMT

please share a sample QVW and exactly what you need. We can be able to help you ..

Re: Dealing with change over time function

sunny_talwar — Fri, 26 Jun 2015 19:04:43 GMT

Something like this:

When you make a selection you still see the percentage like below

Expression: =Sum(Sales)/If(Sum(Sales) > 0, Above(Sum({<Year=>} Sales)))-1

Note sure if this is what you wanted, but if you did this is what I did: I made the denominator and numerator in pieces (check them in the attached application and see how they impact when I make selections. Breaking a problem into pieces makes it easier to solve.

HTH

Best,

Sunny

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 19:20:11 GMT

It works on a line graph, but not in a KPI where only one date is referenced.

Re: Dealing with change over time function

sunny_talwar — Fri, 26 Jun 2015 19:26:35 GMT

Doing it in a KPI (Text Box Object) should be easier. See if this is what you want:

Text Box Expression: =Num(Sum({<Year = {"$(=Max(Year))"}>}Sales)/Sum({<Year = {"$(=Max(Year) - 1)"}>} Sales)-1, '#,##0.%')

HTH

Best,

Sunny

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 19:32:56 GMT

I will try this. Thank you. But, I just noticed that the value in the most recent period is not correct. The computed value I get is -0.0088 whereas it should be -2.33*10-5. The base values are:

2014- 21.942353

2015 - 21.94184

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 19:33:08 GMT

The latter periods are computing correctly.

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 19:35:11 GMT

The KPI worked!

Thank you!

Re: Dealing with change over time function

sunny_talwar — Fri, 26 Jun 2015 19:36:43 GMT

Awesome

Anything else we need to work on?

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 19:43:29 GMT

yes, do you know how to reduce the trailing decimals in the variables? Current the output values have trailing decimals to the 6th place. I know there's a trim statement, but I'm not sure where to use it. Would I need to create a measure for each variable?

Re: Dealing with change over time function

sunny_talwar — Fri, 26 Jun 2015 19:45:17 GMT

Reduce this? 21.942353 You can use Round(21.942353, 0.01) which will give you 21.94

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 19:46:27 GMT

should I use that in the load step?

Re: Dealing with change over time function

sunny_talwar — Fri, 26 Jun 2015 19:49:33 GMT

You can if you want to round the numbers when you bring them, but the downfall is that you loose precision of decimals in your application. If precision is not important I would drop them off, if it is, I would use front end formatting to see only two decimals (if that is what you want) using Num() function

Num(21.942353, '#,##0.00') will give you 21.94 -> benefit is you still have the precision

Best,

Sunny

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 19:52:00 GMT

So I would need to create a measure for each of the variable? Cool!

Thanks,

Jes

Re: Dealing with change over time function

sunny_talwar — Fri, 26 Jun 2015 19:54:14 GMT

Hahahaha, seems like you got your solution, did you??? I am kind of confused about what exactly are you trying to do? If you know what you want know, you don't have to spend your energy explaining it, but if you still need help, I would like if you can elaborate

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 20:03:16 GMT

Yes, sorry for the confusion. Thank you again for all your help! Actually, the change function also works. It was my error.

Re: Dealing with change over time function

jleefjcapital — Fri, 26 Jun 2015 20:04:19 GMT

Thank you, but I have already been helped. Have a great day!

Re: Dealing with change over time function

sunny_talwar — Fri, 26 Jun 2015 20:05:17 GMT

No problem

Have a wonderful weekend.

Best,

Sunny