Problem with variable in set expression

[Anonymous]

Hi,

I have a problem with a SET analysis variable, it won't evaluate through more than the last year.

I have data like:

Year	Month	Data
2013	Jan	25000
2013	Feb	37000
...	...	..
2013	Dec	42000
...	...	...
2018	Sep	102500

.. and so on through all months up until today.

• The first thing I want to evaluate is, if there is 12 months in the year, sum Data for that year in december. So, the sum of 2013 should be 42000.
• Second, if there's no twelfth month, sum Data for the last month in that year, in this case it should sum to 102500 for 2018.
• Third, if I make a selection in the month dimension, let's say I pick April. The sum of Data should be for april for all years.

I have a field in my script (MonthVariable) that puts this data on every year-month: jan-13, feb-13, mar-13 and so on..

This script will output my desired month/year data:

Date(Max(MonthVariable),'MMM-YY') - Let's call it Monthlookup

BUT:

When I use this script to sum the results: Sum({$<MonthVariable={"$(=Date(Max(MonthVariable),'MMM-YY'))"}>} Data)

I get this output:

Year	Monthlookup	Sum of data
2013	dec-13	0
2014	dec-14	0
2015	dec-15	0
2016	dec-16	0
2017	dec-17	0
2018	sep-18	102500

It works as desired when I choose months, but why doesn't it evaluate all the years?

[neelamsaroha157]

Hi, Can you share the sample data with required output. It would be easier to see which part is not working.

[rwunderlich]

You cannot use Set Analysis for this case.  The set expression is evaluated only once per chart, not row by row.

-Rob

[Anonymous]

Thanks Rob, do you have any suggestion how to solve the problem in another way?

[captain89]

Hi,

try to do this:

aggr(Sum({$<MonthVariable={"$(=Date(Max(MonthVariable),'MMM-YY'))"}>} Data), Year) or

sum(aggr(Sum({$<MonthVariable={"$(=Date(Max(MonthVariable),'MMM-YY'))"}>} Data), Year) )

it's horrible but it may runs.

[rwunderlich]

sum(if(num(MonthVariable)=aggr(NODISTINCT Max(MonthVariable),Year), Data))

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[Anonymous]

Sorry, I think I was unclear of the results I was looking for. I want to sum only the last month, not the entire years total.So let's say I have 97500 customers in dec-17 and 102500 customers in aug-2018, thats the result I'm looking for. (I have data for each month, but I'm only interested in the customers at the end of the year).
Your suggestion works fine Rob, although I get the totals for each year.

[captain89]

I'll get the desidered result with Rob's suggestion.

I suggest:

create a field in script with yearmonth in number

such as :

load *,

year(date)*100+month(date)*1 as YearMonthNum,

then take Rob's suggestion:

sum(if(YearMonthNum=aggr(NODISTINCT Max(YearMonthNum),Year), Data)

[rwunderlich]

My solution calculates data only for the ;last month -- using my test data.  So perhaps there is an issue in your data or a misunderstanding here. Can you post a sample qvw?

-Rob

[neelamsaroha157]

This alternate may also work for you -