topic Another Question regarding the date selection in QlikView

Another Question regarding the date selection

Wed, 16 Feb 2011 10:18:40 GMT

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

Another Question regarding the date selection

Wed, 16 Feb 2011 10:26:06 GMT

Search on Forum "Create Master Calendar"

Re:Another Question regarding the date selection

Wed, 16 Feb 2011 10:29:31 GMT

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.

Re:Another Question regarding the date selection

Miguel_Angel_Baeyens — Wed, 16 Feb 2011 10:43:54 GMT

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.

Re:Another Question regarding the date selection

Wed, 16 Feb 2011 10:48:45 GMT

Excuseme....

Try

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

Re:Another Question regarding the date selection

Wed, 16 Feb 2011 12:10:46 GMT

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

Re:Another Question regarding the date selection

Miguel_Angel_Baeyens — Wed, 16 Feb 2011 12:42:20 GMT

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.

Re:Another Question regarding the date selection

Wed, 16 Feb 2011 14:17:58 GMT

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?

Re:Another Question regarding the date selection

Miguel_Angel_Baeyens — Wed, 16 Feb 2011 21:36:21 GMT

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.

Re:Another Question regarding the date selection

Wed, 16 Feb 2011 21:43:43 GMT

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)

Re:Another Question regarding the date selection

Miguel_Angel_Baeyens — Wed, 16 Feb 2011 21:48:04 GMT

Hi,

Using your expression, having a variable range, the most appropiate thing would be to get the dates those weeks are. You can use weeks in variables as ranges, either:

Sum({< TIME_PERIOD_WEEK_NO = {'>=$(vWeekPreFrom)<=$(vWeekPreTo)'} >} WK_SALES_GROSS_REV)

Hope that helps.

Re:Another Question regarding the date selection

Wed, 16 Feb 2011 22:10:18 GMT

Ah Miguel, There you go you just hit the Bulls Eye 🙂 Looks like finally I achieved what I was trying to do. Thank you again for all the help and I wish I too have as much experience as you..so how much time did it take for you to gain this experience in QV?

Thanks by Heart and please keep posting and supporting new users like me.

ANDY

Re:Another Question regarding the date selection

Wed, 16 Feb 2011 22:34:34 GMT

Hello Miguel,

Looks like the problem is back or rather I thought it was all over before comparing the numbers and have identified that its not correct. Can you please take a look at the image here. In one table I have 3 variables calculated separately and in other table I used the expression as suggested by you by creating another variable PreTo but the numbers show different the totals for the products highlighted dont match on both charts, which means the second one isnt doing what its supposed to do? Looks like the second chart shows the numbers as soon as I enter the From Week and nothing happens after the To value is entered. You think I am doing something worng here?

Please help 😞

Re:Another Question regarding the date selection

Miguel_Angel_Baeyens — Wed, 16 Feb 2011 22:42:44 GMT

Difficult to say without taking a look at the expression and dimensions.

Re:Another Question regarding the date selection

Wed, 16 Feb 2011 22:52:18 GMT

I wish I could send something but cannot and will try to explain, I have a product dimension (shows the name) and an expression as below using Variable for the first chart, which seems to be working fine.

sum

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

And the second chart contains the same product dimension with the expression as suggested by you

Sum({< TIME_PERIOD_WEEK_NO = {'>=$(VPreFrom)<=$(vPreTo)'} >} WK_SALES_GROSS_REV)

Hope this helps. In the second chart I was expecting to calculate the sum of sales for weeks 4-6 and if it did then the totals on both should match for one product.

Re:Another Question regarding the date selection

Miguel_Angel_Baeyens — Wed, 16 Feb 2011 23:20:39 GMT

Make sure that vPreFrom is written properly, QlikView is case sensitive.

Try to select manually in field TIME_PERIOD_WEEK_NO from vPreFrom to vPreTo and see whether that returns in the graph as expected.

Hope that helps.

Another Question regarding the date selection

tanelry — Thu, 17 Feb 2011 10:43:49 GMT

Just to expand possibilities,
Another approach for "multiple value variables" is to load "island fields" that are not connected to any other fields.
And for dynamic number of columns it is possible to use pivot table.

See this example: I have loaded three independent week fields for user selection (_WeekPre, _WeekPromo, _WeekPost)
Pivot table uses "normal" week and product dimensions and additional calculated dimension:
=ValueList('Pre','Promo','Post','Result')

Now expression uses SET syntax to return values for different week sets according to selection.
Nested if bock acts as pointer to ValueList dimension.

if(ValueList('Pre','Promo','Post','Result')='Pre',
 num(sum({<Week=p(_WeekPre)>} Sales),'# ##0'),
if(ValueList('Pre','Promo','Post','Result')='Promo',
 num(sum({<Week=p(_WeekPromo)>} Sales),'# ##0'),
if(ValueList('Pre','Promo','Post','Result')='Post',
 num(sum({<Week=p(_WeekPost)>} Sales),'# ##0'),
num(sum({<Week=p(_WeekPost)>} Sales) / sum({<Week=p(_WeekPre)>} Sales) - 1,'# ##0%')
)))

Note that I also used format functions within expression, so the Sales is integer but Result is percentage (Chart properties > Number format is set to Expression default).

Another Question regarding the date selection

Thu, 17 Feb 2011 11:20:44 GMT

Hello Tanel,

What you suggested definetly seems to be a solution for my problem here. Can you attach the QVW from where you have posted the above screen shot please?

Thanks a ton in Advance

Another Question regarding the date selection

tanelry — Thu, 17 Feb 2011 11:50:41 GMT

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;

Another Question regarding the date selection

Thu, 17 Feb 2011 13:03:01 GMT

Hello Tanel,

Amazing its exactly what I have expected, but when I apply all the settings as mentioned by you, this is what I see as in the image, Can you please suggest what could be wrong. Also as the product list is huge so I want to have a calculation condition when clicked on a button or something and, I want to limit the weeks selection for 3 for each Pre, Promo, Post can you help with the restriction conditions as mentioned here please. A BIG THANK YOU for your time and patience.