topic Sum Trends in QlikView

Sum Trends

alanmcgrath — Wed, 10 Sep 2014 14:31:37 GMT

Hi All,

I'm trying to sum or count trends within a data set. I have a table with the Dimension Client_# and the expressions: 1.Savings value for the current month for each client- Sum(Savings). 2.Savings value for last month for each client - Sum({<Report_Mo={'$(vPreviousMonth)'}>}Savings). 3. Comparison of the first two - If(Last Month savings is less than this month, 'Trend up', If Last month savings is greater than this month, 'Trend Down', 'Trend Even'.

All that works fine, but now I want 3 text objects which will display the count of clients Trending up, Trending Down, and Trending Even.

Seems like a simple thing but I'm just stuck.

Any help with this is appreciated.

Thanks!

Client_#	Last MonthSavings	Current Month Savings	Trend
Client 1	3	2	Trend Down
Client 2	3	3	Trend Even
Client 3	1	1	Trend Even

Re: Sum Trends

datanibbler — Wed, 10 Sep 2014 14:48:38 GMT

Hi Alan,

I guess the simple solution

>> COUNT({$<Trend = {'Trend Down'}>} Client_#) <<

won't work? Else I guess you would already have done it.

Can you specify why it won't work or what error_message you have, if any, or so?

Best regards,

DataNibbler

Re: Sum Trends

cheburashka — Wed, 10 Sep 2014 14:52:18 GMT

Hello Alan.

In the textbox you can't refer to to the straight table that you have build.

Or you can try to rebuild a virtual table in the expression of the textbox and do a count of your Trend field for a particular value. ( Check out the Aggr function on how to build the "virtual table" )

Or you can precalculate the Trend field in the script

, KR Koen

Re: Sum Trends

alanmcgrath — Wed, 10 Sep 2014 14:56:26 GMT

DataNibbler - I didn't try that. Trend is not a field in my database but rather calculated, can I still reference it in Set Analysis this way?

My Table works fine, but when I try to do each of the counts, I get a blank.

Koen - thanks, I'll look into the "virtual table"

Re: Sum Trends

datanibbler — Wed, 10 Sep 2014 15:01:49 GMT

No, you can't - Koen said it, I forgot: You can refer to the expressions used in your table only within the table, not in separate objects - there you'd have to re-calculate the trend, but that should not be too hard, you can still use set_analysis, sth. like this

>> COUNT({$<[Current month savings] = {"$(= '<' & [Last month savings])"}>} Client_#) <<

Re: Sum Trends

alanmcgrath — Wed, 10 Sep 2014 15:11:32 GMT

Not sure I'm doing it correctly,I plugged in my expressions in place of Current Month Savings and Last Month Savings.@ I'm using the below:DataNibbler

COUNT({$<Savings = {"$(= '<' & Sum({<Report_Mo={'$(vPreviousMonth)'}>}Savings))"}>} Client_#)

This produces a count of 91 which is the total of Clients

Re: Re: Sum Trends

jaimeaguilar — Wed, 10 Sep 2014 16:00:41 GMT

Hi,

please check the attached example. You can use this formulas in your text boxes:

='Clients w/ Trend Down: ' &

count({$<Client_#={"=sum({$<MonthName={'Aug 2014'}>} Savings)/sum({$<MonthName={'Sep 2014'}>} Savings)>1"}>} DISTINCT Client_#)

within set analysis you compare if previous month savings / current savings are either >, = or < than 1. (these 3 cases will represent down, even and up trend). So in every text box you count the number of clients that fall into any of these categories.

In my example, for sake of simplicity I used static set analysis for months, you'll just need to substitute that part with your $(vPreviousMonth) variable,

regards

Re: Sum Trends

alanmcgrath — Wed, 10 Sep 2014 16:29:56 GMT

Awesome, Super helpful!! Thats working for the most part...just one more wrinkle. There are 4 values for savings 0,1,2,3. It's not counting those with a 0 in one of the months because it gives an error when dividing by 0. So if this month was 3 and last month was 0 it should be counted as trend up but its skipping over that. Will an IF statement help to count those as well or would you suggest a different approach?

Thanks!

Re: Sum Trends

jaimeaguilar — Wed, 10 Sep 2014 16:40:20 GMT

You can avoid the division by zero issue modifying the formula like this:

'Clients w/ Trend Down: ' &

count({$<Client_#={"=sum({$<MonthName={'Aug 2014'}>} Savings) > sum({$<MonthName={'Sep 2014'}>} Savings)"}>} DISTINCT Client_#)

'Clients w/ Trend Even: ' &

count({$<Client_#={"=sum({$<MonthName={'Aug 2014'}>} Savings) = sum({$<MonthName={'Sep 2014'}>} Savings)"}>} DISTINCT Client_#)

'Clients w/ Trend Up: ' &

count({$<Client_#={"=sum({$<MonthName={'Aug 2014'}>} Savings) < sum({$<MonthName={'Sep 2014'}>} Savings)"}>} DISTINCT Client_#)

this way you just compare both sums without having to perform the division and it will count even those cases where current month savings are zero

Re: Re: Re: Sum Trends

cheburashka — Wed, 10 Sep 2014 16:48:20 GMT

Hello Jaime,

Awesome solution.

Tell me if i'm wrong, you are using an advanced search expression in your set-analysis to set the values of the [Client_#] field?

Advanced search expression:

"sum({$<MonthName={'Aug 2014'}>} Savings)/sum({$<MonthName={'Sep 2014'}>} Savings)>1"

,KR Koen

Re: Sum Trends

jaimeaguilar — Wed, 10 Sep 2014 17:05:31 GMT

Yes, this is an advanced search, so you filter just the coincidences that you're looking for. In this example it was clear that the search has to be applied to Client_# Field. However, in more complex data models it can be a little tricky to determine which field is the best to apply the search.

Just to add something to this post, the searches within set analysis normally use double quotes ". Also you need to start the search with an = sign, otherwise it won't work. And finally the search has to be compared or equalled to some value / expression in order to work correctly,

regards

Re: Sum Trends

alanmcgrath — Wed, 10 Sep 2014 17:09:18 GMT

Many thanks! Been trying to figure this out forever.

Re: Sum Trends

cheburashka — Wed, 10 Sep 2014 17:21:53 GMT

Hello Jaime,

I've used searches before in set-analysis but they looked like: {$< year ="=201*">}

If I'm correct you used the search in the set-analysis to build a condition based upon other fields.

I'm going to remember this one.

Do you have some blogposts about advanced searches in set analysis?

,Koen

Re: Sum Trends

jaimeaguilar — Wed, 10 Sep 2014 18:15:30 GMT

Hi,

Right now I can't remember of a post regarding that topic. However, if you have a specific example, you may contact me and I can help you,

regards