Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help required in writing a Variable!

Hi

I have test box in which I want to show the 'Selected Week-(Value in the input box)'and'Selected Week'. If no week is selected then it shows the maximum week in the 'Selected Week' and 'Selected Week-(Value in the input box)'.


Selected Week: (This works fine)

=If(GetSelectedCount([Week]) < 1, VCFYFW, Max([Week]))

Week - Contains week
VCFYFW - Current Financial Year and Financial Week
vSWeek - vSelectedweek

Selected Week-(Value in the input box): (This is what I want)

I want to do something similar like above but we need to consider vWeek which is coming from the input box.

For example:

1. If no week is selected it should consider VCFYFW - vWeek.

2. If only one week is selected, then consider vSWeek(=If(GetSelectedCount([Week]) < 1, VFYFW, Max([Week])))) - vweek.

Any idea how to write and put it in a variable like above(=If(GetSelectedCount([Week]) < 1, VCFYFW, Max([Week]))).

For Example:

If I select 201101 in the week and input box contains 5. then I should get '201049'(Selected Week-(Value in the input box)) and 201101(Selected Week).

Any idea how to achieve this?

9 Replies
gandalfgray
Specialist II
Specialist II

Put this in the textbox:

=WeekYear(MakeWeekDate(Left(if(GetSelectedCount(Week)<1, VCFYFW, Max(Week)),4), Right(if(GetSelectedCount(Week)<1, VCFYFW, Max(Week)),2))-vWeek*7)&Num(Week(MakeWeekDate(Left(if(GetSelectedCount(Week)<1, VCFYFW, Max(Week)),4), Right(if(GetSelectedCount(Week)<1, VCFYFW, Max(Week)),2))-vWeek*7),'00')

Not applicable
Author

Thanks Goran

Your code works fine! Only problem is that when I select 201048 and vWeek=5 it should should show 201044 but whereas your code gives 201043.

Also when the user selects 201026 and vWeek=5 but the overall week that is available week is only 201023 then the result of the above code should be only the same(201023).

Could you please look into it further if you have free time. I need to understand how this code works then only I can proceed further.

Not applicable
Author

Can u pls share a sample app so that we could a feel of the problem, as, though scenario is understood, the sample data would help in better learning.

Regards,

-Karthik

Not applicable
Author

Hi Goran

I think I have managed to solve the below problem by doing vNumWeeks1-1 instead of vNumWeeks1. Hope the changes that I have is correct.

"Your code works fine! Only problem is that when I select 201048 and vWeek=5 it should should show 201044 but whereas your code gives 201043."


=WeekYear(MakeWeekDate(Left(if(GetSelectedCount(Week)<1, VCurrentFYFW, Max(Week)),4), Right(if(GetSelectedCount(Week)<1, VCurrentFYFW, Max(Week)),2))-(vNumWeeks1-1)*7)&Num(Week(MakeWeekDate(Left(if(GetSelectedCount(Week)<1, VCurrentFYFW, Max(Week)),4), Right(if(GetSelectedCount(Week)<1, VCurrentFYFW, Max(Week)),2))-(vNumWeeks1-1)*7),'00')


If the changes that I have done is correct then for the below problem I think we need to consider Min(Week) if (SelectedWeek - vWeek) < Min(Week). I am not sure where should I add in your code.

Could you please check that and let me know.

Not applicable
Author


Karthik G wrote:
Can u pls share a sample app so that we could a feel of the problem, as, though scenario is understood, the sample data would help in better interrogation. <div></div>


Thanks for your response!

But I am Sorry! I know it helps if the data is there. But I cannot the attach the data. So please try to help me out with the details given by me.

gandalfgray
Specialist II
Specialist II

Hi Attitude

Glad my solution could help you!

One way to ensure the returned Week is not outside the existing Weeks in your app is like this:

Add a NumMax around your previous expression.

Calculate the minimum existing Week regardless of any selections done. (The {1} inside the Min({1} Week) is a Set expression that makes any selections disregarded.)

=NumMax(Min({1} Week),WeekYear(MakeWeekDate(Left(if(GetSelectedCount(Week)<1, VCFYFW, Max(Week)),4), Right(if(GetSelectedCount(Week)<1, VCFYFW, Max(Week)),2))-(vWeek-1)*7)&Num(Week(MakeWeekDate(Left(if(GetSelectedCount(Week)<1, VCFYFW, Max(Week)),4), Right(if(GetSelectedCount(Week)<1, VCFYFW, Max(Week)),2))-(vWeek-1)*7),'00'))

Not applicable
Author

Thanks Goran

I tried with the latest code that you have given but it didn't seems to work. It gives the same value as before. If possible Could you please check and let me know if you have is any other solution.

gandalfgray
Specialist II
Specialist II

Hi Attitude

Well, my expression works for me.

Please post your expression if you need more help!

/G

Not applicable
Author

Well I am using the same expression that has been given by you... Not sure why it is not working for me...