topic Year-to-Date issue: format in QlikView

Year-to-Date issue: format

Wed, 21 Sep 2011 11:33:05 GMT

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

Year-to-Date issue: format

Wed, 21 Sep 2011 12:06:38 GMT

Hello David,

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

Regards

sravan

Re: Year-to-Date issue: format

Wed, 21 Sep 2011 12:45:08 GMT

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

rwunderlich — Wed, 21 Sep 2011 14:34:21 GMT

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

-Rob

http://robwunderlich.com

Year-to-Date issue: format

Thu, 22 Sep 2011 12:12:00 GMT

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

Re: Year-to-Date issue: format

Thu, 22 Sep 2011 12:15:55 GMT

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

Re: Year-to-Date issue: format

Thu, 22 Sep 2011 12:44:49 GMT

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

Re: Year-to-Date issue: format

Thu, 22 Sep 2011 13:41:20 GMT

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

Year-to-Date issue: format

Fri, 23 Sep 2011 07:00:02 GMT

Hi Walters,

Check out this tutorial on YTD calculation using Set Analysis

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

Rgrds,

Abhinava

Re: Year-to-Date issue: format

Thu, 29 Sep 2011 11:01:49 GMT

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