Hi,
I am facing challenge to create particular pivot table and taking those values in text box.
Pivot table
| Material_1 | A | A | B | B | C | C | D | D | E | E |
| DB.Fiscal Quarter | Qty Sold | Qty mfg | Qty Sold | Qty mfg | Qty Sold | Qty mfg | Qty Sold | Qty mfg | Qty Sold | Qty mfg |
| Q1-FY18 | - | - | - | - | 0 | 25 | 0 | 78 | 0 | 122 |
| Q2-FY18 | 0 | 3 | - | - | 0 | 48 | 0 | 78 | 0 | 63 |
| Q3-FY18 | 0 | 13 | - | - | 0 | 25 | 0 | 15 | 0 | 41 |
| Q4-FY18 | 0 | 6 | 0 | 100 | - | - | - | - | 0 | 187 |
| Q1-FY19 | 1 | 4 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 70 |
Expected output
| Material_1 | A | A | A | B | B | B | C | C | C | D | D | D | E | E | E |
| DB.Fiscal Quarter | Qty Sold | Qty mfg | Last mfg qty | Qty Sold | Qty mfg | Last mfg qty | Qty Sold | Qty mfg | Last mfg qty | Qty Sold | Qty mfg | Last mfg qty | Qty Sold | Qty mfg | Last mfg qty |
| Q1-FY18 | - | - | | - | - | | 0 | 25 | 25 | 0 | 78 | 78 | 0 | 122 | 122 |
| Q2-FY18 | 0 | 3 | 3 | - | - | | 0 | 48 | 48 | 0 | 78 | 78 | 0 | 63 | 63 |
| Q3-FY18 | 0 | 13 | 13 | - | - | | 0 | 25 | 25 | 0 | 15 | 15 | 0 | 41 | 41 |
| Q4-FY18 | 0 | 6 | 6 | 0 | 100 | 100 | - | - | - | - | - | - | 0 | 187 | 187 |
| Q1-FY19 | 1 | 4 | 4 | 1 | 0 | 100 | 1 | 0 | 25 | 1 | 0 | 15 | 1 | 70 | 70 |
Here i want to create a column with following conditions
1) if Qty sold Q1 FY 19 >0 and Qty mfg >0 then show same Qty mfg in all quarters
2) if Qty sold Q1 FY 19 =0 then show 0 Qty mfg in Q1 FY 19
3) if Qty sold Q1 FY 19 >0 and Qty mfg <=0 then show Qty mfg in last /max Quarter mfg value in Q1FY19 and rest quarters to show respective mfg value
4) then show all the summation of Q1 FY 19 mfg value in text box
