Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Walters,
Check out this tutorial on YTD calculation using Set Analysis
http://community.qlik.com/qlikviews/1062
Rgrds,
Abhinava
Hello David,
what do you get if you use Left(Periode,4) as Year and Right( Periode,2) as Month?
Regards
sravan
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
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
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
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
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
Hi Walters,
Check out this tutorial on YTD calculation using Set Analysis
http://community.qlik.com/qlikviews/1062
Rgrds,
Abhinava
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