Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

lidiavonkrond
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.

test set analysis las year.png

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 noYearWeekYrWkFiscal yearsum(  [Quantity return line])
Total70
10057201662016-06201621
10057201672016-07201619
10057201562015-06201516
10057201572015-07201514

thanks in advance

Lidia

1 Solution

Accepted Solutions
lidiavonkrond
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
cesaraccardi
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

lidiavonkrond
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)]:

LOAD

       // 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

Date18-02-2016
Fiscal year2016
Month02
Period2
Week7
Year2016
YrMth2016-02
YrPer2016-02
YrWk2016-07
Day18
WeekDayThu
DateSeqNr42418
YrPerSeqNr66
YrWkSeqNr269
QuarterQ1
YrQtr2016-Q1
%YrWk201607
_cntCalendar1
_indCalenderType4W
Last Fiscal year2015
LastYear2015
LaYrCuWk2015-07
T009B.BUMON02
YrMthSeqNr62
YrQtrSeqNr22

thanks again

Lidia

cesaraccardi
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] )

lidiavonkrond
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

lidiavonkrond
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.

lidiavonkrond
New Contributor III

Re: set analysis - last year more then 1 week

i found the solution

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

Thanks for helping

Lidia

Community Browser