Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

wilkempe
New Contributor II

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
wilkempe
New Contributor II

Re: Problem with variable in set expression

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

10 Replies
neelamsaroha157
Valued Contributor II

Re: Problem with variable in set expression

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

MVP & Luminary
MVP & Luminary

Re: Problem with variable in set expression

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

-Rob

wilkempe
New Contributor II

Re: Problem with variable in set expression

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

captain89
Contributor

Re: Problem with variable in set expression

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.

MVP & Luminary
MVP & Luminary

Re: Problem with variable in set expression

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

wilkempe
New Contributor II

Re: Problem with variable in set expression

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
Contributor

Re: Problem with variable in set expression

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)

MVP & Luminary
MVP & Luminary

Re: Problem with variable in set expression

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
Valued Contributor II

Re: Problem with variable in set expression

This alternate may also work for you -