Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Year-to-Date issue: format

Hi,

Can anyone advise on this issue?

I'm new to qlikview so I'm surely overlooking something here. :-)

I load below data: 

Cloture_PL:

LOAD

     Periode,

     Scenario_2,

     Scenario_3,

     Montant,

     [Project Code],

     [Type PMO],

     text([Demand ID])&'BE' as [Accounting_ID]

FROM

$(vQvdFolder)Cloture_PL.qvd

(qvd);

The format in the inputfile for field Periode is 201101;201102;201103;... referring to the Year and Month.

I want to include a year to date calculation of sum(Montant) in a pivot with set analysis.

I guess the problem is that 'Periode' is not interpreted by QV as a date.

When trying functions like Date(), Makedate(), right(Periode,4) as Year, left(Periode,2) as Month I can't get out a usable date format.

And the YTD calculation results in 0...

Any help would be great!

Thanks and regards,
David

1 Solution

Accepted Solutions
Not applicable

Year-to-Date issue: format

Hi Walters,

Check out this tutorial on YTD calculation using Set Analysis

http://community.qlik.com/qlikviews/1062

Rgrds,

Abhinava

9 Replies
Not applicable

Year-to-Date issue: format

Hello David,

what do you get if you use Left(Periode,4) as Year and Right( Periode,2) as Month?

Regards

sravan

Not applicable

Re: Year-to-Date issue: format

Hi Walters,

What you can do is create an Year and Month field along with a period, like in the following code and then use it for your calculation.

Cloture_PL:

LOAD

     Periode,

     left(Periode,4) as Year,

     right(Periode,2) as Month,

     Scenario_2,

     Scenario_3,

     Montant,

     [Project Code],

     [Type PMO],

     text([Demand ID])&'BE' as [Accounting_ID]

FROM

$(vQvdFolder)Cloture_PL.qvd (qvd);

Rgrds,

Abhinava

Re: Year-to-Date issue: format

date(date#(Periode, 'YYYYMM'), 'YYYYMM') as Periode

-Rob

http://robwunderlich.com

Not applicable

Year-to-Date issue: format

Hi all,

Thanks a lot for your help!

I made a combination of above answers and loaded:

Cloture_PL:

LOAD

     date(date#(Periode, 'YYYYMM'), 'YYYYMM') as Periode,

     left(Periode,4) as PL.Year,

     right(Periode,2) as PL.Month,

     Scenario_2,

     Scenario_3,

     [Libellé compte],

     Montant,

     [Project Code],

     [Type PMO],

     text([Demand ID])&'BE' as [Accounting_ID]

FROM

$(vQvdFolder)Cloture_PL.qvd

(qvd);

I'm not able to do the year to date calculation with set analysis though.

Below formula provides me with the sum per month, but not the year-to-date:

=num(Sum ({$<PL.Year = {$(=only(PL.Year))},PL.Month={"<=$(=max(PL.Month))"}>}Montant))

Any ideas?

Thanks and best regards,

David

Not applicable

Re: Year-to-Date issue: format

Hi all,

Thanks a lot for your help!

I made a combination of above answers and loaded:

Cloture_PL:

LOAD

     date(date#(Periode, 'YYYYMM'), 'YYYYMM') as Periode,

     left(Periode,4) as PL.Year,

     right(Periode,2) as PL.Month,

     Scenario_2,

     Scenario_3,

     [Libellé compte],

     Montant,

     [Project Code],

     [Type PMO],

     text([Demand ID])&'BE' as [Accounting_ID]

FROM

$(vQvdFolder)Cloture_PL.qvd

(qvd);

I'm not able to do the year to date calculation with set analysis though.

Below formula provides me with the sum per month, but not the year-to-date:

=num(Sum ({$<PL.Year = {$(=only(PL.Year))},PL.Month={"<=$(=max(PL.Month))"}>}Montant))

Any ideas?

Thanks and best regards,

David

Not applicable

Re: Year-to-Date issue: format

Hi Walters,

for year to date you just need to omit the month expression in the set analysis expression.

try the following code.

=num(Sum ({$<PL.Year = { ">=$(=only(PL.Year))", "<=$(MaxYear)"} >} Montant))

where MaxYear=max(PL.Year), the maximum year value in PL.Year

PS. Remember Year to date is always calculated for the current year.

Regrds,

Abhinava

Not applicable

Re: Year-to-Date issue: format

HI David,

I see that you did not define the Variable

Let vMaxYear = max(PL.Year) in the script.

You can define in script or in the Settings-> Document Properties-> Variables

PS: I still find that that is not the only Problem.. Let me further see where the problem is.

Regards

Sravan

Not applicable

Year-to-Date issue: format

Hi Walters,

Check out this tutorial on YTD calculation using Set Analysis

http://community.qlik.com/qlikviews/1062

Rgrds,

Abhinava

Not applicable

Re: Year-to-Date issue: format

Hello everyone,

Thank you all for your help on this issue!

The issue is resolved by defining the same calendar and date variables as in above set analysis tutorial.

Then I linked the information in both PL_Cloture and Calendar with below function:

date(date#(Periode, 'YYYYMM'), 'YYYYMM') as Periode

Best regards,

David

Community Browser