set analysis - last year more then 1 week

[lidiavonkrond]

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

thanks in advance

Lidia

[cesaraccardi]

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]

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

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

[cesaraccardi]

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]

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]

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

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

[lidiavonkrond · Accepted solution]

i found the solution

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

Thanks for helping

Lidia