Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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)