Can you check this expression please?

[#1]

Hello All,

Unable to figure out if there is something wrong in this expression here:

=sum({$<TIME_PERIOD_WEEK_NO = "Week(Today())-52">}WK_SALES_GROSS_REV)

I am trying to calculate the rolling weekly sales amount. So I want to do a Current week - 51 weeks at any point of time on a chart. I already posted, but could not get any reply for this. I am sure someone from you might have done this before I am currently in week 9 of 2011 and I want to show the data for this 9 weeks till the week 10 of 2010.

Please help.

[Miguel_Angel_Baeyens]

Hello Andy,

This expression is always going to show the last 52 weeks until today, since you are actually using Today() function in your set analysis. So can you please upload some sample data or elaborate on what you want to get?

Anyway, the date comparison in set analysis modifiers can be used as a range like the following

=sum({$<TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))<=$(=Date('01/01/2011'))'},FORECAST_TYPE= {'Q0F'}>} FCT_GROSS_REVENUE)/1000

The example is silly, but may help you understand how that works a date greater than or equal to today one year ago until the first day of 2011.

Hope that helps.

[#3]

Hello Miguel,

Once again I appreciate your patience in responding to queries and helping people like me. The below is what I see when I use the above expression: The problem is the FORECAST Line shows for the whole year ignoring the Today() as the data is planned and available for the whole year before the begin of the year. I have the below expressions as seen below,

Forecast =sum({$<TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))'},FORECAST_TYPE= {'Q0F'}>}FCT_GROSS_REVENUE)/1000

Actual =sum({$<TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))'} >} WK_SALES_GROSS_REV)/1000

HOPE this helps as my QVW file is huge and cannot attach a sample for this to work...

[Miguel_Angel_Baeyens · Accepted solution]

Hello Andy,

Why don't you use the expression I showed above? Something like

Sum({< TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))<=$(=Date(Today()))'}, FORECAST_TYPE = {'Q0F'} >} FCT_GROSS_REVENUE) / 1000

Although I'm using Today() to force exactly 52 weeks ago starting the count today, you can specify any other date, for example, the maximum date for "Actual" data, if it's not today.

Hope that helps.

[#5]

Hello Miguel,

I just wanted to let you know the actual requirement I had before trying anything, as I was buzy working on a huge Chart :-), anyways it worked and thanks for the help.

I have another question though, I have 2 hierarchies for customers, NAL2 and NAL3. I have more than 500 in NAL2 and 5000 in NAL3. The requirement is I need to build a Tracker for a Group of NAL2 and some under this for NAL3 in a Pivot. Currently I am doing this using the Valuelist and by a calculated Dimension. But its taking a lot of time as the chart has many calculated dimesions and If statements. Can you suggest a better way to do this? As in how can I do a Grouping of NAL2 and NAL3 customers and create a Dimension at the script level, so that All i have to do is use this Dimension and then write a simple expression to calclucalte the sales for a period...Hope you undestood the issue, The below is the sample set analysis query I am using for one customer, and I will send you more info if required.

if(ValueList('BOOTS')='BOOTS',sum({$< NPD_ID = {'NULL'},TIME_PERIOD_YEAR ={$(#VCurYear)}, TIME_PERIOD_PERIOD ={'2'},NAL2_ID = {'6170'}, NAL3_ID = {'9316'}, FORECAST_TYPE = {'CURRENT'} >}FCT_GROSS_REVENUE)+sum({$< TIME_PERIOD_YEAR ={$(#VCurYear)}, TIME_PERIOD_PERIOD ={'2'},NAL2_ID = {'6170'}, NAL3_ID = {'9316'}>}WK_SALES_GROSS_REV),

Like above I have a grouping of 45 customers and 4 different Pivots to show the details, I want to create this gouping at the script level and use the customer names as a Dimension to check if that increases the performance..

Thanks in Advance,

ANDY

[Miguel_Angel_Baeyens]

Hello Andy,

Since the question is different and seems not related to the current one, I recommend you to create a new thread in the forum with the new question.

Besides, I'm not quite sure to understand waht you need. Are those hierarchies NAL2 and NAL3 actual fields in your tables? If so, why do you need to use a synthetic dimension (using ValueList). Or is that you only have a code (NAL3_ID) and you want to create a text for each ID? Is there any relationship between NAL2 and NAL3 (I assume there is at least one, the customer ID)?

I think that you can use a pivot table with NAL2 and NAL3 and dimensions, and one expression for each forecast and actual sales.

Is that right?

[#7]

Hello Miguel,

As suggested I have created a new Thread, can you check this and suggest please?

http://community.qlik.com/forums/p/44074/175187.aspx#175187

Kind Regards,

ANDY