If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Dynamic set between two members

Started by oscarca, 10 Dec 2021 02:57:16 AM

Previous topic - Next topic

oscarca

Hi Cognos Community !

Is it possible to create a dynamic set between two members ?
The fiscal calendar is Apr -> March therefore I am leading 3 to convert standard calendar to fiscal

I have two expressions, one that will return the start of the year i.e. 202104:
lead(item(head(descendants(ancestor([pPeriod],2),2),1),0),3)

and a second one that will return the end of the year i.e. 202203:
lead(item(tail(descendants(ancestor([pPeriod],2),2),1),0),3)

Now I would like to create a set between does two members returning (202104,202105,202106,202107........... 202203).

If not possible, what would could be an alternate solution to achieve this ?

All tips, ideas and help are greatly appreciated.

CognosPaul

There are a few ways you could achieve this.

In your example you're looking for all of the descendants of the year on the specific level, so descendants(ancestor([pPeriod],2),2) does exactly what you're describing.

But let's say the second member isn't necessarily the last member of the level, so instead of December you want all of the months up until October.

Do something like this:
periodsToDate([Year Level],[EndMember])

This will return all of the months from January to October.

But what if the first member is ALSO variable. You want everything from March to October

Again, there are a few ways we can do this. You'll use periodsToDate and except twice. I'll construct the expression step by step to explain what it's doing

periodsToDate([Level],[StartMember]) <- This will return Jan Feb Mar
except(periodsToDate([Level],[StartMember]),[StartMember]) <-Returns Jan Feb
except(periodsToDate([Year Level],[EndMember]),except(periodsToDate([Level],[StartMember]),[StartMember])) <- Returns Mar, Apr, May, Jun, Jul, Aug, Sep, Oct

Let me know if you have trouble with it.

oscarca

#2
Thanks Paul for the reply,

except(periodsToDate([Sales Orders].[Calendar Order Date].[Calendar Order Date.Year - Quarter - Month - Day].[Year]
,lead(item(tail(descendants(ancestor([pPeriod],2),2),1),0),3)),except(periodsToDate([Sales Orders].[Calendar Order Date].[Calendar Order Date.Year - Quarter - Month - Day].[Year]
,lead(item(head(descendants(ancestor([pPeriod],2),2),1),0),3)),lead(item(head(descendants(ancestor([pPeriod],2),2),1),0),3)))

But it only gave me a set of 202203 - 202201 when selecting 202112
the desired result would be 202203(end of fiscal year) -> 202104 (start of fiscal year)

oscarca

#3
The tricky part here is that its not a regular year like Jan -> Dec, it is fiscal year April -> March

CognosPaul

How is your hierarchy set up? Is it a standard calendar year, Jan-Dec, or Fiscal Apr-Mar?

oscarca

#5
It is a standard Gregorian calendar Jan-Dec

I did come up with a solution that is really ugly (can certainly be rewritten) that I can post later today when I get home.

oscarca

So the solution I came up with looks like this (It seems to work but is hella ugly):
set(
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),3),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),4),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),5),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),6),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),7),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),8),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),9),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),10),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),11),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),12),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),13),
lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),14)
)

CognosPaul

Hey, ugly works.

How about this:

lastPeriods(-11,lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),3))

oscarca

Works like a charm Paul and much less code !
Thanks mate !

MFGF

Quote from: CognosPaul on 14 Dec 2021 08:32:51 AM
Hey, ugly works.

How about this:

lastPeriods(-11,lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),3))

Elegant and very impressive, my old friend! :-)

MF.
Meep!