Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
darrin_pilkingt
Contributor II

Previous Month set analysis not working but with a twist.

The twist being that in my calendar I have created serial numbers for my months to simplify the going forward and back in time.

Example, current month =52

Each month from the start of my calendar counts from 1 on.

I am trying t compare current revenue to previous month revenue with Set Analysis.

The two formulas below, I believe should work based on my searches but both return 0.

=sum({<TradMthSerial = {$(=max(TradMthSerial)-1)}>} RevenueAgent)

=sum({<TradMthSerial = {$(=only(TradMthSerial - 1))}>} RevenueAgent)

Thank you for you help.

1 Solution

Accepted Solutions
MVP
MVP

Re: Previous Month set analysis not working but with a twist.

Hi

If I understand correctly, you are making a selection in TradMthDescShort - so you will need to override this selection in your set expression. Something like:


=sum({<TradMthDescShort= ,TradMthSerial = {$(=max(TradMthSerial)-1)}>} RevenueAgent)


HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
19 Replies
Not applicable

Re: Previous Month set analysis not working but with a twist.

Darrin,


If you throw those expressions from your set definition:

=max(TradMthSerial)-1

=only(TradMthSerial - 1)


into text objects do they evaluate to the results you expect?

darrin_pilkingt
Contributor II

Re: Previous Month set analysis not working but with a twist.

Clark, thanks for the quick reply.

I do.  with the current month = 52 I receive 51 with both max() and only().  And as I select each prior month I see this value decrease as I would expect.

whiteline
Honored Contributor II

Re: Previous Month set analysis not working but with a twist.

Hi.

Try to add quotes:

=sum({<TradMthSerial = {'$(=max(TradMthSerial)-1)'}>} RevenueAgent)

How have you created the  TradMthSerial exactly ?

darrin_pilkingt
Contributor II

Re: Previous Month set analysis not working but with a twist.

Whiteline, unfortunately that did not solve it.  This seems so simple, not sure what's causing it not to work.

SerialDates:

For the record, this idea comes from Rob Wunderlich.  I cannot take credit.

I have my calendar created, then I sort by DateKey and use AutoNumber() to create my serialized values for weeks, months, quarters, etc.  Typically it simplifies the rolling forward and back quite well.

$(vTableName):

LOAD

    *,    

     AutoNumber(SalesYr & SalesDayOfYr, '_DaySerial') as _SalesDaySerial,

     AutoNumber(SalesYr & SalesWk, '_WkSerial') as _SalesWkSerial,

     AutoNumber(SalesYr & SalesMth, '_MthSerial') as _SalesMthSerial,

     AutoNumber(SalesYr & SalesQtr, '_QtrSerial') as _SalesQtrSerial

Resident tCalendar

Order By DateKey;

MVP
MVP

Re: Previous Month set analysis not working but with a twist.

Try

AutoNumber(SalesMth, 'SalesMth' ) as _SalesMthSerial

and use

=sum({$<_SalesMthSerial = {'$(=Max(_SalesMthSerial))'}>}RevenueAgent)

whiteline
Honored Contributor II

Re: Previous Month set analysis not working but with a twist.

Strange. Actually it should work with or without quotes if the TradMthSerial is just a number (not string or dual).

Have you tried to test with a static value instead of $-sign expansion ?

Not applicable

Re: Previous Month set analysis not working but with a twist.

Darrin,

What happens with this expression?


=sum({1<TradMthSerial = {$(=max(TradMthSerial)-1)}>} RevenueAgent)

darrin_pilkingt
Contributor II

Re: Previous Month set analysis not working but with a twist.

I think I figured out why this was not working. 

I set my list box to show the month short description.  So when I was selecting Apr I would think my formula would work as Apr = 52 and 52-1 = 51 or Mar.


MthShortDesc:  Feb, Mar, Apr

TradMthSerial:   50,51,52


Now if I select 52 directly this formula now works.  Sorry.  But what I would like to do is select it's MthShortDesc equivalent.  Which looks much nicer for the end user.

Would I need to use p()?

darrin_pilkingt
Contributor II

Re: Previous Month set analysis not working but with a twist.

This was an option I thought might work with this.  But unfortunately not.

=sum({$<TradMthDescShort = p({$<_TradMthSerial={'$(=max(_TradMthSerial)-1)'}>}TradMthDescShort)>} Sales)

Community Browser