I'm looking at this, but I don't understand what you need.
Are you looking to assign some value for each state? The workbook opens to "Gross Receipt Forecast" sheet. What am I seeing here?
Yes, I want to assign some value for each state. In this case, We want to assume that for some states, the sales will grow by % comparing to the original receipt.
Instead of setting up parameters for each state and create calculation field for all states. Is there another way to do it? for example, Can I do a list of state for a parameter and just select and input for state values?
Sorry for the confusion. Please see attached. I hide all unnecessary sheets.
again, thank you so much for your help!
Parameter Assign Value.twbx 43.3 KB
If I understand what you need correctly ...
You can have a second data source where you maintain your adjustment factors.
I created an excel file from your data. (Attached.) It has one row per state. I grabbed some field from your data that seemed to have a value in most rows, and I renamed it [Factor].
And then I made a second data source from that.
I blended on STATE, so when you are using "Input Template" as your primary data source, you can pull the associated [Factor] value for each state like I did in Sheet 3. Once I started adding fields from your original source, that made your source the primary data source. Then I clicked on my secondary source, and I dragged [Factor] onto the sheet. (I changed it to DISCRETE so that it displays as a text value in ROWS.) Then I put SUM(Projected...]) on the sheet. |
Next I made a calc [Adjusted Projected ...], and in that calc I just multiply sum([Projected ...]) by sum([secondary source].[factor]). I have displayed that calc on the sheet as well.
If you need to maintain those multiplication factors, it is just a matter of editing the excel file. You won't need 50 separate parameters to handle it.