topic Re: Load query: Year in QlikView

Load query: Year

knightwriter — Tue, 12 Jan 2016 08:15:20 GMT

Hi All,

My dashboard showed only one years worth of data in 2015. I now want it to show 2016 and the same 2015 data. I have created the selection filters but when I reload the script only a small part of this data shows for both years.

I am using the below script and seek any guidance to fix this issue?

LOAD * FROM \\sample.qvd (qvd)

Where (InYear([Planned Date], Today(), 0) or InYear([Planned Date], AddYears(Today(), 1), 0))

Re: Load query: Year

tamilarasu — Tue, 12 Jan 2016 08:24:50 GMT

Try,

LOAD * FROM

sample.qvd (qvd)

Where Year([Planned Date])=Year(today()) or Year([Planned Date]) =Year(Today())-1;

Re: Load query: Year

Anonymous — Tue, 12 Jan 2016 08:26:05 GMT

your where clause refers to 2016 (first part) and 2017 (second part with addyears(today(),1))

if you want your plkanned data for 2015 and 2016 you Need to modify the second part to

or InYear([Planned Date], AddYears(Today(), -1), 0))

do you Show only planned data or do you want to include actual data as well?

Re: Load query: Year

Anonymous — Tue, 12 Jan 2016 08:26:28 GMT

like this?

LOAD * FROM \\sample.qvd (qvd)

Where (InYear([Planned Date], Today(), 0) or InYear([Planned Date], AddYears(Today(), -1), 0))

Re: Load query: Year

Peter_Cammaert — Tue, 12 Jan 2016 08:28:59 GMT

LOAD * FROM \\sample.qvd (qvd)

WHERE Year([Planned Date]) >= (Year(Today()) - 1);

Best,

Peter

Re: Load query: Year

knightwriter — Tue, 12 Jan 2016 08:33:09 GMT

Apologies, typo on my behalf - Yes, I use AddYears(Today(), -1), 0)).

I believe I have to change all the text object properties too?

Currently I have:

Count(Distinct If([Status]='Completed' AND InYear([Date], Today(), 0), [Id], ))

Do I need to include the "Or" function to include my 2015 data?

Re: Load query: Year

knightwriter — Tue, 12 Jan 2016 09:03:51 GMT

Thanks Peter,

I have now included this in the script but need to update the Text Object Properties. My equation is rather long and I know its not showing the correct result. Is there a way to shorten this?

=Num (Count(Distinct If([Company.Car Order]='BMW' AND [Status]='Completed' AND InYear([Completed Date], Today(), 0) or InYear([Completed Date], Today(), -1), [Id], )), '###,###')