Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Problem with variable in set expression

Hi,

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

I have data like:

YearMonthData
2013Jan25000
2013Feb37000
........
2013Dec42000
.........
2018Sep102500

.. 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:

YearMonthlookupSum of data
2013dec-130
2014dec-140
2015dec-150
2016dec-160
2017dec-170
2018sep-18102500

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

10 Replies
neelamsaroha157
Specialist II
Specialist II

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

Anonymous
Not applicable
Author

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

captain89
Creator
Creator

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

Anonymous
Not applicable
Author

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
Creator
Creator

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Specialist II
Specialist II

This alternate may also work for you -