Solved: Another Question regarding the date selection - Qlik Community

Report Inappropriate Content · ‎2011-02-16

Hello Experts,

How do i write a Variable which allows me to select more than 1 week to sum up the sales. Currently I have created a variable and used it in the chart which allows me to input a number or select from drop down which calculates the sales, however I want to create a variable where i select 3 weeks, it should sum up the sales for 3 weeks instead of 1 week.

Thanks in Advance

tanelry · ‎2011-02-17

No, I just experimented on another sales app (which is huge).

But I revealed almost every detail about this approach, I think you're cool to build it yourself.

The script part is simply three tables

WeekPre:
load
rowno() as _WeekPre
AutoGenerate 52;

WeekPromo:
load
rowno() as _WeekPromo
AutoGenerate 52;

WeekPost:
load
rowno() as _WeekPost
AutoGenerate 52;

View solution in original post

Report Inappropriate Content · ‎2011-02-16

Search on Forum "Create Master Calendar"

Report Inappropriate Content · ‎2011-02-16

Hello Paco,

Appreciate your quick reply, however I have a calender which is working and the weeks derived out of it which is the solution to my problem but halfway. I want to know how to select more than one week in a variable to calculate the sales. I can do it with a multi box, but how can I use that as a variable value? If you read my original post may be this will give you more information.

Miguel_Angel_Baeyens · ‎2011-02-16

Hello,

If you want to use a date range, you may check this file. There are several other ways, though, for example using the Concat() function to return some values and use them in a set analysis:

LET vWeeks = Concat(DISTINCT Week, ','); Sum({< Week = {$(vWeeks)} >} Price)

Hope this helps.

Report Inappropriate Content · ‎2011-02-16

Excuseme....

Try

sum({$<week={$(=GetFieldSelections(week)+GetFieldSelections(week)+1)}>}Sales)

Report Inappropriate Content · ‎2011-02-16

Hello Miguel and Paco,

Thank you for both the responses, but I am unsure as to how this can fit my requirement. So I have attached a image of what I am trying to deliver if that give you more information, I tried attaching a sample QVW but due to restrictions here I cannot do it, but can send this image. Please take a look and suggest whats the best approach to achieve it.

Andy

Miguel_Angel_Baeyens · ‎2011-02-16

Hello Andy,

If I understand that correctly, that's not possible since a variable can store only one value. If you want a range, then you need a slider/calendar object, where you can store the starting week in a variable and the end week in a different variable, and the range will be the list of possible values between both. An inputbox doesn't allow you to select more than one value.

If the Inputbox is a requirement, then you will have to create two variables for each possible week (Pre Promotion Start Week, Pre Promotion End Week, and so).

Hope that helps.

Report Inappropriate Content · ‎2011-02-16

Hello Miguel,

I dont know how it works, but 3 sliders for 3 selections holding 53 weeks each... I think it wont be impressive...to look at...But how do i create 2 variables for each field From Week and To Week and start using that?? Can you help me more with that please?

Miguel_Angel_Baeyens · ‎2011-02-16

Hello Andy,

I think I didn't make myself clear. There are two options, first would be using sliders (one per date), second would be using two variables for each date type.

Please refer to the file I posted above, where you can see how this two variable per range approach works and how to implement in the charts.

Hope that helps.

Report Inappropriate Content · ‎2011-02-16

Apologise for taking so much of your time, But can you help me further with this? Now I have 1 variable and 1 value, say I will have 2 variables for From Week and To Week then how to change the expression which I have now like , what if i enter 3 in from week and 4 in to week ? I want to see the sales rev for this 2 weeks...

sum({$<TIME_PERIOD_WEEK_NO = {'$(VPre)'}>}WK_SALES_GROSS_REV)