Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks,
Sachin