topic Re: Problem with variable in set expression in QlikView

Problem with variable in set expression

Anonymous — Tue, 11 Sep 2018 09:55:19 GMT

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?

Re: Problem with variable in set expression

neelamsaroha157 — Tue, 11 Sep 2018 20:31:28 GMT

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

Re: Problem with variable in set expression

rwunderlich — Tue, 11 Sep 2018 22:10:51 GMT

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

-Rob

Re: Problem with variable in set expression

Anonymous — Wed, 12 Sep 2018 06:48:49 GMT

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

Re: Problem with variable in set expression

captain89 — Wed, 12 Sep 2018 15:22:47 GMT

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.

Re: Problem with variable in set expression

rwunderlich — Wed, 12 Sep 2018 17:09:14 GMT

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

Re: Problem with variable in set expression

Anonymous — Thu, 13 Sep 2018 07:07:51 GMT

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.

Re: Problem with variable in set expression

captain89 — Thu, 13 Sep 2018 15:32:42 GMT

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)

Re: Problem with variable in set expression

rwunderlich — Thu, 13 Sep 2018 18:43:43 GMT

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

Re: Problem with variable in set expression

neelamsaroha157 — Thu, 13 Sep 2018 19:00:18 GMT

This alternate may also work for you -

Re: Problem with variable in set expression

Anonymous — Fri, 14 Sep 2018 10:14:14 GMT

After playing around a little with your script I realized i didn't have to be that advanced.

I have another field "MonthID" i.e: (201809), in the same table as Data, which always contains the last month of data.

I made a script like this:

Max(Aggr(Sum(CustCounter),MonthID))

So much fuss when the answer was quite simple. Hadn't used the Aggr-function before though.

Thanks a lot Rob, Matteo and Neelam for looking into this