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: 
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
Author

Hi Walters,

Check out this tutorial on YTD calculation using Set Analysis

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

Rgrds,

Abhinava

View solution in original post

9 Replies
Not applicable
Author

Hello David,

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

Regards

sravan

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

http://robwunderlich.com

Not applicable
Author

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
Author

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
Author

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
Author

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
Author

Hi Walters,

Check out this tutorial on YTD calculation using Set Analysis

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

Rgrds,

Abhinava

Not applicable
Author

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