Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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

Contributor
Contributor

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

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