# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

New Contributor III

## set analysis - last year more then 1 week

Hello who can help me with this one

posible calender selections

Year = Year

periode= Periode

FiscalYear-Wk = YrWk.

When i select 1 YrWk everything reacts as expected

When i select more then 1 week in the same fiscal year - last year does respond with 0

Data last fiscal year is available.

in table

DG # 2016 =if(  sum( {\$< \$(vOnlyLastYear) >} [Value return line])  =0, null(),  sum( {\$< \$(vOnlyLastYear) >} [Quantity return line]) )

DG # 2015= if(  sum( {\$< \$(vOnlyLastYear) >} [Value return line])  =0, null(),  sum( {\$< \$(vOnlyLastYear) >} [Quantity return line]) )

in script

 let vOnlyCurYear = replace('Year={"~(=Only( [Fiscal year]) )"}',  '~','\$'); let vOnlyLastYear = replace('Year={"~(=Only( [Last Fiscal year]) )"} ,YrWk={"~(=LaYrCuWk)"}, Period= ','~','\$');

 Article no Year Week YrWk Fiscal year sum(  [Quantity return line]) Total 70 10057 2016 6 2016-06 2016 21 10057 2016 7 2016-07 2016 19 10057 2015 6 2015-06 2015 16 10057 2015 7 2015-07 2015 14

Lidia

1 Solution

Accepted Solutions
New Contributor III

## Re: set analysis - last year more then 1 week

i found the solution

YrWk={"\$(=concat(DISTINCT LaYrCuWk,' ","  '))"}

Thanks for helping

Lidia

6 Replies
Valued Contributor

## Re: set analysis - last year more then 1 week

Hi Lidia,

I believe the issue lies on this set modifier YrWk={"~(=LaYrCuWk)"}, try using a function to concatenate the multiple values when you have more than 1 week selected:

YrWk={"~(=Concatenate(LaYrCuWk,','))"},

--Cesar

New Contributor III

## Re: set analysis - last year more then 1 week

Hi Cesar,

Thanks for the quick reaction,

for fast test i changed it in the object to

=sum( {\$<

Year={"\$(=Only( [Last Fiscal year] )) "},

YrWk={"\$(=Concatenate(LaYrCuWk, ',')) "}

>} [Quantity return line] )

it gives a 0

aswel on 1 selection as on multiple selection

do you have any other idea's  there welcome

can it be that YrWk is a string and not a number?

below calendar

[\$(vSection)]:

// Keys

left(YrWk,4)&num(Right(YrWk,2),'00') as %YrWk,

//Calculated

left(YrWk,4)-1&'-'&num(Right(YrWk,2),'00') as LaYrCuWk,

Year-1 as LastYear,

[Fiscal year] -1 as [Last Fiscal year],

// Attributes

*

FROM \$(vSourcePath1)\$(vSourceQvd1).qvd (qvd);

field example

 Date 18-02-2016 Fiscal year 2016 Month 02 Period 2 Week 7 Year 2016 YrMth 2016-02 YrPer 2016-02 YrWk 2016-07 Day 18 WeekDay Thu DateSeqNr 42418 YrPerSeqNr 66 YrWkSeqNr 269 Quarter Q1 YrQtr 2016-Q1 %YrWk 201607 _cntCalendar 1 _indCalenderType 4W Last Fiscal year 2015 LastYear 2015 LaYrCuWk 2015-07 T009B.BUMON 02 YrMthSeqNr 62 YrQtrSeqNr 22

thanks again

Lidia

Valued Contributor

## Re: set analysis - last year more then 1 week

Hi Lidia,

No problems. Maybe try replacing it with explicit values to check if the issue is indeed with this set modifier:

=sum( {\$<

Year={"\$(=Only( [Last Fiscal year] )) "},

YrWk={"2016-07","2016-06"}

>} [Quantity return line] )

New Contributor III

## Re: set analysis - last year more then 1 week

that works for the next options

=sum( {\$<

//        Year={"\$(=Only( [Fiscal year] )) "},

YrWk={"2016-07","2016-06"}

>} [Quantity return line] )

=sum( {\$<

//             Year={"\$(=Only( [Last Fiscal year] )) "},

YrWk={"2015-07","2015-06"}

>} [Quantity return line] )

but NOT with concatenate , not for 1 selection and not by multiply selections

=sum( {\$<

//           Year={"\$(=( [Last Fiscal year] )) "},

//             YrWk={"2015-07","2015-07"}

YrWk={"\$(=Concatenate(LaYrCuWk,','))"}

>} [Quantity return line] )

=sum( {\$<

//            Year={"\$(=( [Fiscal year] )) "},

//             YrWk={"2016-07","2016-07"}

YrWk={"\$(=Concatenate(YrWk, ',')) "}

>} [Quantity return line] )

any idea's left

New Contributor III

## Re: set analysis - last year more then 1 week

=Concat(DISTINCT LaYrCuWk &',')&'0000-00'

this works in text field but i don't get it right.

New Contributor III

## Re: set analysis - last year more then 1 week

i found the solution

YrWk={"\$(=concat(DISTINCT LaYrCuWk,' ","  '))"}

Thanks for helping

Lidia