Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
hmjason7
New Contributor II

Set Analysis

Hi All,

I have a problem with the syntax for my set analysis expression in relation to dates. I am trying to create a reusable expression in the load script, as follows:

Set vCurTargetWeek =

                                        Sum({<CurYTDFlag={'1'},

                                        Year={'=$(vCurFiscalYear)'},

                                        FiscalWeek={'=$(vCurFiscalWeek)'}>}    Sales)

where:

Set vCurFiscalYear = "num(Year(Max({<CurYTDFlag ={1}>} FiscalYear)))";

Set vCurFiscalWeek =   "Div(today()-YearStart(today(),0,4),7)+1-1";

So the vCurTargetWeek will simply give me the sum of Sales for a particular year and week, where vCurFiscalYear  will for example equal 2015, and vCurFiscalWeek will equal a week number, like 30 or 40 etc.

I believe the Year part works fine, but I am unsure why the same wouldn't apply for the FiscalWeek part?

Any ideas would be most appreciated.

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Set Analysis

For starters remove the = characters in front of the dollar expansions and the single quotes aren't necessary either if the variables return numbers. And dollar expansion is also done in the script so you need to work around that:

SET vCurTargetWeek = Sum({<CurYTDFlag={'1'},Year={@(vCurFiscalYear)},FiscalWeek={@(vCurFiscalWeek)}>}Sales);

LET vCurTargetWeek = replace('$(vCurTargetWeek)','@','$');


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
Highlighted
sasiparupudi1
Honored Contributor III

Re: Set Analysis

Try

let vCurFiscalYear = 'num(Year(Max({<CurYTDFlag ={1}>} FiscalYear)))';

let vCurFiscalWeek =   'Div(today()-YearStart(today(),0,4),7)+1-1';

let vCurTargetWeek ='Sum({<CurYTDFlag={1},Year={$'&'('&vCurFiscalYear&')},FiscalWeek={=$'&'('&vCurFiscalWeek&')}>}    Sales)';

Highlighted
MVP & Luminary
MVP & Luminary

Re: Set Analysis

For starters remove the = characters in front of the dollar expansions and the single quotes aren't necessary either if the variables return numbers. And dollar expansion is also done in the script so you need to work around that:

SET vCurTargetWeek = Sum({<CurYTDFlag={'1'},Year={@(vCurFiscalYear)},FiscalWeek={@(vCurFiscalWeek)}>}Sales);

LET vCurTargetWeek = replace('$(vCurTargetWeek)','@','$');


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
hmjason7
New Contributor II

Re: Set Analysis

Thanks for the reply. I just tried it, but doesn't work

Highlighted
sasiparupudi1
Honored Contributor III

Re: Set Analysis

please try the following

let vCurTargetWeek ='Sum({<CurYTDFlag={1},Year={$'&'(='&vCurFiscalYear&')},FiscalWeek={$'&'(='&vCurFiscalWeek&')}>}Sales)';

as Gysbert says there is = character that needs to be removed in the expression.

hth

Sasi

Highlighted
hmjason7
New Contributor II

Re: Set Analysis

Hey,

It doesn't work I think the equals sign is still necessary somewhere in order to evaluate the vCurFiscalYear and vCurFiscalWeek variables?

Highlighted
MVP & Luminary
MVP & Luminary

Re: Set Analysis

No, they're not necessary. In fact, the = signs will make sure you won't get a correct result. Try substituting them with hardcoded values like 2015 and 19. I reckon you won't get a correct result then either, because the problem is something else. It would help if you could post a small qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
Highlighted
hmjason7
New Contributor II

Re: Set Analysis

Yes I think you're right.

The issue then I guess is how I have defined the vCurFiscalYear  and vCurFiscalWeek variables.

Because currently they are:

Set vCurFiscalYear = "num(Year(Max({<CurYTDFlag ={1}>} FiscalYear)))";

Set vCurFiscalWeek =   "Div(today()-YearStart(today(),0,4),7)+1-1";

I should be trying to evaluate them such that they return me the numbers intended.

So if I:

Let vCurFiscalWeek =   "Div(today()-YearStart(today(),0,4),7)+1-1";

this will give me the week number directly. But because vCurFiscalYear  has a set analysis component, I can't do the same for vCurFiscalYear ?

Highlighted
hmjason7
New Contributor II

Re: Set Analysis

Hey,

The workaround I have done to get the vCurFiscalYear  is simply create a new (isolated) table where I am simply picking up the Max(Year) of the mastercalendar, where the CurYTDFlag = 1.

Then I can simply just do:


Let vCurFiscalYear  = Peek('MaxYear',0, 'FindMaxFiscalYear') ;


which will for example give me 2015. Then, the vCurTargetWeek will work.


Thanks for the help