Another Question regarding the date selection

[#1]

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

[Miguel_Angel_Baeyens]

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.

[#3]

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

[#4]

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 😞

[Miguel_Angel_Baeyens]

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

[#6]

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.

[Miguel_Angel_Baeyens]

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.

[tanelry]

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).

[#9]

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

[tanelry · Accepted solution]

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;

[#11]

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.