Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I have come across a requirement in which the the product X has a lag of 2 days.
So when i filter the report for Week = 9/9/2017, then the Product X must show values for the week 9/11/2017.
Filter applied | Week:9/9/2017 | |
Expected Output | ||
X Week | Week | QTY |
9/11/2017 | 9/9/2017 | 100 |
9/11/2017 | 9/9/2017 | 200 |
9/11/2017 | 9/9/2017 | 300 |
original Output | ||
X Week | Week | QTY |
9/9/2017 | 9/9/2017 | 80 |
9/9/2017 | 9/9/2017 | 90 |
9/9/2017 | 9/9/2017 | 110 |
Any help with this would be appreciated.
Thanks,
Harshala
Hi Sunny, i tried the below expressions earlier it doesn't work.
Sum({1<date={"$(=date(num(date)+1))"}>}Value)
also the other expression :
=Below(TOTAL Sum({1<date={"$(=date(num(date)+1))"}>}Value)) - doesn't work if i remove the date field.
Solution: i need ignore the date selection
Sum({< weekdate = {"$(=date(Num(Weekdate)+2,'MM/DD/YYY'))"} ,date= >}QTY)
Hi,
As a dimension X Week, add calculated dim : =AddMonths(Week,2)
I think you might have missed the 2 days part... Shouldn't it be Week + 2 for 2 days?
Hi,
This is a weekly report. All the fields are calculated at week level.
There are four different products, all the products must be filtered for the selected week date except for the product X.
so when i select week = 9/9/2017 (MM/DD/YYYY)). The Product X should be filtered for the week 9/11/2017 (MM/DD/YYYY) - ie: Week starting on Monday rather than saturday.
Yes, i confused date format DD/MM/YYYY and MM/DD/YYYY because i use to handle with DD/MM/YYYY, and it's late here
=date(Week+2,'MM/DD/YYYY')
Hi Sunny,
Yes, it should be to add 2 days.
I have worked on an expression like date(Num(Weekdate)+2,'MM/DD/YYY') - this works fine as an calculated dimension. But fails in set analysis :
Sum({< weekdate = {"=$(=date(Num(Weekdate)+2,'MM/DD/YYY'))"}>}QTY)
Not sure I understand, but may be you need this
Above(Sum({<weekdate>} QTY), 2)
or this
Above(TOTAL Sum({<weekdate>} QTY), 2)
Let me put it this way :
Week date values : 09/02/2017, 09/09/2017, 09/23/2017, 09/30/2017
All these week are calculated starting from Saturday in a month.
All products are filtered in these week dates.
Except for X, as X is calculated on weekdays starting from Monday. in this case i.e: 09/11/2017.
So i need to write a condition in my set analysis so as to get the qty for product X in such a way that if the user selects 09/09/2017, the product X is filtered for 09/11/2017 and show the QTY for week date 09/11/2017.
I am still not 100% sure I understand... would you be able to share a sample to show the kind of data you have and explain the output you are looking to get from it?
In the sample file:
Date selected: 4/17/2014
sum(Value) = 100
Expected result;
Next day value: sum of value for 4/18/2014 should be displayed.
Every time i make a date selection , the value should be filtered for the next date.