Need to

[#1]

Hi Qlikview Community, I need your help to create a new object in one of my dashboards, I'm a QV beginner and I'm trying to create a new metric in a straight table but I can't find the right expression to populate the % of states Declining. The main issue is that the table has territory as Dimension and the metric is calculated for the % of states within that territory.

This is the output that I'm trying to create:

Territory	Growth(%)	% of States Declining
Nation	0%	=2/5
North	100%	=0/3
South	-67%	=2/2

I was able to show the growth in sales by Territory and now I'm trying to add a metric that represents the percentage of states within that region that have been declining in growth.

For example:

-Northern territory has 3 states (A,B,C) and non of them has decline it's growth, that value that i wan to show is 0%

-Southern territory has 2 states(D,E) and all of them have been declining their growth, the value that I wan to show is 100% or (2/2)

- The nation has 5 states (A-E) and 2 out of 5 have been declining their growth, the value that I want to show is 40%

This is how my raw data looks like:

Each row is a record for the sales for a state with a year attribute and a sales metric where we have duplicated entries for each territory.

Territory	State	Year	Sales
North	A	2014	25
North	A	2014	25
North	B	2014	40
North	C	2014	30
North	A	2013	10
North	B	2013	20
North	C	2013	30
South	D	2014	20
South	E	2014	10
South	D	2013	40
South	E	2013	50

Please let me know if you have additional questions, your help will be much appreciated.

[#2 · Accepted solution]

Example attached, your data doesnt include nation but this should work.

Felim

!

[#3]

Hi Diego,

How is the Growth % calculated? Is it only for Current and Previous Year sales?

Regards,

Prabhu

[#4]

Yes,growth is only calculated for the current and previous year with the expression pasted below:

(Sum({<[Year]={$(=Max([Year]))}>}[Sales])

-Sum({<[Year]={$(=Max([Year]-1))}>}[Sales]))

/

Sum({<[Year]={$(=Max([Year]-1))}>}[Sales])

[#5]

Diego the attachment above should answer your issue

[#6]

Thank you!!!, this is working now!! My data doesn't include nation but it just supposed to be the sum of both territories. Your help is much appreciated

[#7]

I have an additional question, I understand the need to create the aggr additional table, but I still don't understand why we need to add the SUM term at the beginning of the statement?

SUM(AGGR(Sales, Territory, State)

Wouldn't this sum all the records by territory and not by State??

Thanks,