Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a calendar that looks like above. There is a variable named Period. The period kan be tertiary, or quarter or month. When u select 'Måned', then it shows months.
when u select 'Kvartal', it shows quarter and etc...
What I want is to calculate for prior period.
In attchment you can find more details.
in the expression
You forgot to attach. However, I think you're asking how you can use an island field that dynamically changes what a dimension is to calculate a current/previous period. And the answer is 2 more variables. For example, here's what they would look like for previous:
vPreviousStartDate
=pick(match(Period,'Kvartal','Tertial','Måned'),
quarterstart(vPreviousEndDate),
monthstart(addmonths(vPreviousEndDate,-3)),
monthstart(vPreviousEndDate)
)
vPreviousEndDate
=max(Date)-1 //replace "Date" with your lowest-level Date field name
You can then reference these variables in your UI via set analysis. Don't forget to ignore your actual data selections. So:
sum({<Date={">=$(vPreviousStartDate)<=$(vPreviousEndDate)"},Year,Month,Quarter,Tertial>}Sales)
Regards,
Vlad
Hi,
Sorry for forgotten attachment.
i attach again. I have tried Your suggestions But it does not work in my situation.
So hopefully u can help me out of the problems.
Regards
Loi
The problem is that your calendar is built a bit strangely. If I select Kvartal 1 and 2014, and create a list box of calendar_date, the only available date is 20140331. Since you calendar is set up that way, change the variables to:
vPreviousStartDate
=
date(pick(match(Period,'Kvartal','Tertial','Måned'),
quarterstart(vPreviousEndDate),
monthstart(addmonths(vPreviousEndDate,-3)),
monthstart(vPreviousEndDate)
),'YYYYMMDD')
vPreviousEndDate
=
date(pick(match(Period,'Kvartal','Tertial','Måned'),
quarterstart(date#(min(calendar_date),'YYYYMMDD'))-1,
addmonths(monthstart(date#(min(calendar_date),'YYYYMMDD')),-3)-1,
monthstart(date#(min(calendar_date),'YYYYMMDD'))-1
),'YYYYMMDD')
You also forgot to put a '=' at the beginning of vPreviousStartDate, so make sure you copy the exact formula above this time.
You "expAntallNyeSøknaderForrigePeriode" variable should be updated as follows: count({<ledger_status_group_desc={'Søknad'},year,quarter,tertial,month,monthM,calendar_date_num={">=$(vPreviousStartDate)<=$(vPreviousEndDate)"},is_month_end={'-1'}>} distinct ledger_id)
Regards,
Vlad