Dynamic numbers basis the user selection.(Week number)

[deep2021]

Hi All,

I have a scenario as per the below,

 After user made some filter selections, for the products in  weeks is as follows. (see below Rows 1-7).In the below example, Product A has sales from Week 2, Product B from Week 5 and Product C from Week 1.

Output is shown in Rows 11-13. The Week numbers should align from Week 1 for all the products so that user can do parallel comparative analysis.

Can you please suggest on this?

Thanks

[G3S]

would a pivot table work in this instance? 

[vinieme12]

can you post some sample data in excel that best represents your actual data

[deep2021]

Hi,

PFA sample data for the same. In the sample data I have data for w1 to w12 (week1 to week12) and user has applied some selections.

Thanks

[vinieme12]

As below,  use field  newPeriodGroup in the chart

Main:
load product_segment,subfield(RelativePriorPeriod,':',2) as RelativePriorPeriod, product_segment&num(subfield(RelativePriorPeriod,':',2)) as key, dollar_sales inline [
product_segment,RelativePriorPeriod,dollar_sales
DAIRY ALTERNATIVE (DA),W : 2,3525.8
DAIRY ALTERNATIVE (DA),W : 3,3449.2
DAIRY ALTERNATIVE (DA),W : 4,3747.25
DAIRY ALTERNATIVE (DA),W : 5,3665.15
DAIRY ALTERNATIVE (DA),W : 6,3164.8
DAIRY ALTERNATIVE (DA),W : 9,3465.05
DAIRY ALTERNATIVE (DA),W : 10,3838.75
DAIRY ALTERNATIVE (DA),W : 11,4042.8
DAIRY ALTERNATIVE (DA),W : 12,4199.4
MILK & CREAM,W : 6,7366
MILK & CREAM,W : 7,5437.7
MILK & CREAM,W : 8,5885.05
MILK & CREAM,W : 9,4584.75
MILK & CREAM,W : 10,4808.55
MILK & CREAM,W : 11,10850.65
MILK & CREAM,W : 12,3614.9
YOG DRINK,W : 10,234.45
YOG DRINK,W : 11,274.35
YOG DRINK,W : 12,295.8
YOG SPOON,W : 2,820.6
YOG SPOON,W : 3,449.1
YOG SPOON,W : 4,300.9
YOG SPOON,W : 5,935.75
];

left join(Main)
Load product_segment & num(minwk+iterno()-1) as key
,iterno() as newPeriodGroup 
While minwk+iterno()-1<=maxwk
;Load product_segment
,Min(RelativePriorPeriod) as minwk,
Max(RelativePriorPeriod)  as maxwk
Resident Main
Group by product_segment;

[deep2021]

Hi,

thanks for the response. But sometimes i will have value from w3, sometime w3 that is dynamically and based on user selection. In  that case can you please suggest how to manage to show values from w:1.

Thanks

[G3S]

do you mean only week 3 will be selected by user but you want to show from wk 1?