topic Re: help creating a variable in QlikView

help creating a variable

Fri, 26 Apr 2013 16:34:52 GMT

Hi All,

I need help creating a variable.

I have the following fields:

[Reporting Period Index]

[Reporting Date]

I want to create 2 variables that will give me the start and end dates of the previous reporting period index.

eg.

Currently in reporting period index 103

previous Reporting Period Index = 102

reporting dates for previous period = 27/12/2012 - 31/01/2013.

so i would want the variable to show 27/12/2012

and the same for the end date. so a 2nd variable would show 31/01/2013

Can anyone help me please?

Re: help creating a variable

Gysbert_Wassenaar — Fri, 26 Apr 2013 16:57:33 GMT

Something like this:

vStartPreviousPeriod: min({<[Reporting Date]=,[Reporting Period Index]={$(max([Reporting Period Index])-1)}>} [Reporting Date])

vEndPreviousPeriod: max({<[Reporting Date]=,[Reporting Period Index]={$(max([Reporting Period Index])-1)}>} [Reporting Date])

You'll have to select a reporting date or a reporting period index otherwise you simply get the start and end of the second last period.

Re: help creating a variable

Mon, 29 Apr 2013 08:57:39 GMT

Thanks that worked! but i have come into work this morning to find that they have changed their mind! they now want to report on previous calendar month instead on period.

I have the following previous month variable...

vLastMonth = =Month(AddMonths(Date([Reporting Date]),-1))

But i cant seem to get the variable to work in my expression.

sum({<[Reporting Week]=, [Reporting Date]={'=$(=date(vLastMonth))'},[Reason Group] = {'Upgrade'}>} [Revenue Loss])

Any Ideas?

Re: help creating a variable

Mon, 29 Apr 2013 11:44:44 GMT

Hi, i have managed to do this now.

thanks