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

[tanelry]

Regarding number format, see the expression where I have formatting num(...,'# ##0') . The characters for pattern # ##0 don't come through normally when copied from internet. You have to replace this patterns: in chart properties go to Number tab, select "integer" temporarily, copy the pattern from "Format Pattern" box and put number format back to Expression default. Then go to Expression tab and paste the pattern into the num(...,'# ##0') occurences.

And your second question, - do you want to fix the periods to be always equal number of weeks and always in correct sequence?
In this case you might show only the "week promo" list box to end user and apply selection to WeekPre and WeekPost fields automatically with Field Event Trigger. I mean when user select WeekPromo, it will fire:
Select in field: _WeekPre; Search string: ='(' & concat(_WeekPromo - $(=count(_WeekPromo)),'|') & ')'
Select in field: _WeekPost; Search string: ='(' & concat(_WeekPromo + $(=count(_WeekPromo)),'|') & ')'

Chart calculation condition maybe this? =getselectedcount(_WeekPromo)>1 and getselectedcount(_WeekPromo)<4

[#3]

Hello Tanel,

Thank you for patiently answering my queries and All the Best. I have added points twice to keep your spirit going on. Please keep helping more people like me and I want to Thank even Miguel who took a lot of time to help me with issue. Hope to see you guys again soon until then happy Qlicking.

ANDY

[#4]

Hello Tanel,

Further to my query, the requirement from my client is that he doesnt want to see individual columns for the selected weeks, instead columns with the average sales for selected weeks. (if I select weeks 3,4,5 for pre then the sum of sales/3 to calculate the average) and that way we want to track the performance per product. Can you please help me further with the expression I have like

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

Thanks in Advance

[tanelry]

For fixed number of columns it's easier to use straight table and make formatting in normal way.
Weekly average of "Pre":
sum({<TIME_PERIOD_WEEK_NO=p(_WeekPre)>} WK_SALES_GROSS_REV) / count(_WeekPre)

[#6]

Hello Tanel,

I wish it was fixed number of columns, but thats not the case with my client 😞 and my question is the same functionality with any columns selection, however the calculation should be as I mentioned. If I am selecting 3 weeks for Pre, 2 for Promo and 3 for Post then the calculation should be Pre - sum(sales)/3 (As I selected 3 weeks I want average) and for Promo - sum(Sales)/2 ( as I have only 2 weeks selected) and for Post - sum(sales)/3 ( as i have 3 weeks). Now to show this data in the Chart or Pivot, I just need 4 columns with the total amount s for respective weeks calculation and variance per product and I want to create chart as I have other lines to be added in the chart further.

Hope you can help me with this.

[tanelry]

Well, you said:
"I just need 4 columns with the total amount s for respective weeks calculation and variance per product"
and that's where I would use straight table instead of pivot, see expression example in my last post.

A chart, I guess you want to show sales trend through respective weeks. With week dimension it should work with simple expression:
sum({<TIME_PERIOD_WEEK_NO=p(_WeekPre)>} WK_SALES_GROSS_REV) +
sum({<TIME_PERIOD_WEEK_NO=p(_WeekPromo)>} WK_SALES_GROSS_REV) +
sum({<TIME_PERIOD_WEEK_NO=p(_WeekPost)>} WK_SALES_GROSS_REV)
(just make sure that user won't be able to make overlapping selections at _WeekPre, _WeekPromo, _WeekPost)

[#8]

Hello Tanel,

Hope you can help me with this, please check the below expression and the chart scrrenshot this returns.

if(ValueList('Pre Promotion','During Promotion','Post Promotion','Variance','Index')='Pre Promotion', num(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPre)>} WK_SALES_GROSS_REV)/ count(_WeekPre),'#,##0'),
if(ValueList('Pre Promotion','During Promotion','Post Promotion','Variance','Index')='During Promotion', num(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPromo)>} WK_SALES_GROSS_REV)/count(_WeekPromo),'#,##0'),
if(ValueList('Pre Promotion','During Promotion','Post Promotion','Variance','Index')='Post Promotion', num(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPost)>} WK_SALES_GROSS_REV)/count(_WeekPost),'#,##0'),
if(ValueList('Pre Promotion','During Promotion','Post Promotion','Variance','Index')='Index', num(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPromo)>} WK_SALES_GROSS_REV) / sum({<TIME_PERIOD_WEEK_NO=p(_WeekPre)>} WK_SALES_GROSS_REV) - 1,'#,##0%'),
if(ValueList('Pre Promotion','During Promotion','Post Promotion','Variance','Index')='Variance', num(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPromo)>} WK_SALES_GROSS_REV)/ count(_WeekPre)- sum({<TIME_PERIOD_WEEK_NO=p(_WeekPre)>} WK_SALES_GROSS_REV)/count(_WeekPre),'#,##0'))))))

Now my issue is if you check in the above expression I want to deduct the total amount of During Promotion column amount from the Total of Pre Promotion column amount and show the Variance number. However you can see what this returns here is wrong. The variance amount shows as -16158 where as it should be (36254 - 28243 = 8011). I am selecting 3 weeks for Pre, and 1 week for During, would that matter and moreover this is a Pivot with calculcated Dimension so I dont know how to use something like Column(2) - Column(1) . I think it works fine if I select 3 weeks Pre, 3 Weeks During and 3 Weeks Post, however if it was fixed then I would not have had all these issues as discussed earlier, so they would select anyweeks, but the result should be the total of column 2 - total of column 1 can you please help me with this.

[#9]

I found out whats was the mistake I made. Please ignore this.

[#10]

Hello Tanel,

I am writing to you again as I think you know my whole issue and you have provided me with very good solution so far for my issues. My Question is I have some expressions like this,

if(ValueList('Pre','Promo','Post','Result')='Pre', num(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPre)>}/ count(_WeekPre)WK_SALES_GROSS_REV),'#,##0'),
if(ValueList('Pre','Promo','Post','Result')='Promo', num(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPromo)>}/count(_WeekPromo) WK_SALES_GROSS_REV),'#,##0'),
if(ValueList('Pre','Promo','Post','Result')='Post', num(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPost)>}/count(_WeekPro) WK_SALES_GROSS_REV),'#,##0'),
num(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPost)>}/Count(_WeekPost) WK_SALES_GROSS_REV) / sum({<TIME_PERIOD_WEEK_NO=p(_WeekPre)>} /Count(_WeekPre) WK_SALES_GROSS_REV) - 1,'#,##0%'))))
in the chart, Now if I want to add another Dimension and column in the chart called 'Variance Index %'which would be Post - Pre/ 100 to show in percentage. I was wondering how we can do it, I tried copying the whole expressions for Post and pre and then doing it, also I tried pasting them in variables and using these in the chart, but it doesnt seem to work. Any solution like we use, say if(ValueList(Pre,Post,Promo, Result, Index) = 'Index' 'Pre - Post/100. Please help me with this.

Thanks in Advance

[tanelry]

What is the point of still using "valuelist" dimension? I thought you dropped week dimension and now you're much easier to use separate expression for each set, either in pivot or straight table mode.
Btw, there is mistake in the third row: /count(_WeekPro) should be /count(_WeekPost).
And in the forth row you are dividing inside the set definition, not good.
I think this is better:
num(
(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPost)>} WK_SALES_GROSS_REV) / Count(_WeekPost)) /
(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPre)>} WK_SALES_GROSS_REV) / Count(_WeekPre)) - 1
,'#,##0%')

and for Post - Pre / 100 I think:
num(
((sum({<TIME_PERIOD_WEEK_NO=p(_WeekPost)>} WK_SALES_GROSS_REV) / Count(_WeekPost)) -
(sum({<TIME_PERIOD_WEEK_NO=p(_WeekPre)>} WK_SALES_GROSS_REV) / Count(_WeekPre)))
/ 100
,'#,##0%')

Again, isn't it better to write separate expressions?