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

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.

19 Replies
whiteline
Master II
Master II

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

preminqlik
Specialist II
Specialist II

hi once try this

sum({<TradMthSerial = {$(=max({<Year={$(=Year(addmonths(max(Date),-1)))}>}TradMthSerial)-1)}>} RevenueAgent)

arjunkrishnan
Partner - Creator II
Partner - Creator II

Hai Darrin ,

U May Try To Create the Variable For Month

Previous Month

= Date#(Date(MonthStart(addmonths(max(Include Date Field),-1)),'MMM'))

And Then You Call Your Expression

Anonymous
Not applicable
Author

Interesting,

Both Whiteline and Prem's formulas give the same value as the current month selected.  Not the prior, which is odd based on what I am seeing in the script.

This actually works perfectly if I select the _TradMthSerial value. 

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

We need to select TradMthDescShort and have it grab the prior month.  Select Apr and it pulls  _TradMthSerial = 51.

TradMthDescShort_TradMthSerialRevenueAgent
Feb50431
Mar51328
Apr52285

Thanks for everyone's help.  This community is awesome.

Anonymous
Not applicable
Author

But still not working when selecting the Month Description.

Any other thoughts?

whiteline
Master II
Master II

Could you upload the sample that is not working ?

jonathandienst
Partner - Champion III
Partner - Champion III

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
Anonymous
Not applicable
Author

Jonathan, winner winner chicken dinner.  That did it.  Thank you very much.

So I understand, can you explain how comma then rest works.  I don't think i have seen the set written that way before.

jonathandienst
Partner - Champion III
Partner - Champion III

Darrin

The , just separates the selection for the two fields, maybe you meant the = sign. An  = sign with nothing on the RHS instructs the set to ignore the selections for that field (in this case, TradMthDescShort).

Jonathan

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

Actually that is what it was.  I am familiar with the open ended "=" sign meaning to ignore but I hadn't thought about it in this usage.  Using it to select a value but disregard the selection.

Typically I would use it on a global scale for dates, etc. on a higher level summary.

Thanks again.