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.
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.
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)
The tricky part here is that its not a regular year like Jan -> Dec, it is fiscal year April -> March
How is your hierarchy set up? Is it a standard calendar year, Jan-Dec, or Fiscal Apr-Mar?
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.
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)
)
Hey, ugly works.
How about this:
lastPeriods(-11,lead(item(head(descendants(ancestor(lag([pPeriod],3),2),2),1),0),3))
Works like a charm Paul and much less code !
Thanks mate !
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.