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: 
qvhjenvo
Contributor
Contributor

Prior period that can be a teriary or quarter or month

Calendar screen.PNG.png

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

3 Replies
vgutkovsky
Master II
Master II

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

qvhjenvo
Contributor
Contributor
Author

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

vgutkovsky
Master II
Master II

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