request logic for below requirement

[Anonymous]

Hi,

I have a file with many columns. I need to do the below things.

1) Divide column M/12.( ths can be done in script I guess)

2) I need to populate above value in all the months columns starting from V column. But here need a logic connect  ie based on the start month mentioned in Column Q.

eg: if the Q has Dec 16, I need to populate the values from Dec 16 column. If Q has Dec 18, then populate the value from Dec 18.

Please can some one help on this expression?

I have attached the excel file, I need to get this result in QV app.

Regards,

Sahana

[shiveshsingh]

Hi

for first requirement, do it in script.

and for second requirement, you need to use crosstable. I have just done basic part.

eg: if the Q has Dec 16, I need to populate the values from Dec 16 column. If Q has Dec 18, then populate the value from Dec 18.

What do you mean by this? So you need these values in chart? Let me know the expected output on the value you select.

One more thing, you need to change Year_Month values from integer to Year Month also.

T:

CrossTable(Year_Month, Data, 21)

LOAD F1,

    [Participating University],

    Category,

    [Sub-category],

    [CDL's Name],

    [Sourcing Manager],

    Wave, SPW,

    [Project Name],

    [Project Progress],

    [Supplier Name],

    [Contract Status],

    [Annual Addressable Spend]/12 as [Annual Addressable Spend],

    [Contracted Annual Savings],

    [Line of Sight % Savings],

    [New Contracted Spend],

    [Contract Start Date],

    [Contract End Date],

    [Savings End Date],

    [Contract Year],

    Comments, [42705],

    [2016 Total],

    [42736], [42767],

    [42795],

    [CY1 Total],

    [42826], [42856],

    [42887], [42917],

    [42948], [42979],

    [43009], [43040],

    [43070],

    [2017 Total],

    [43101], [43132],

    [43160],

    [CY2 Total],

    [43191], [43221],

    [43252], [43282],

    [43313], [43344],

    [43374], [43405],

    [43435],

    [2018 Total],

    [43466], [43497],

    [43525],

    [CY3 Total],

    [43556], [43586],

    [43617], [43647],

    [43678], [43709],

    [43739], [43770],

    [43800],

    [2019 Total],

    [43831], [43862],

    [43891],

    [CY4 Total],

    [43922], [43952],

    [43983], [44013],

    [44044], [44075],

    [44105], [44136],

    [44166],

    [2020 Total],

    [44197], [44228],

    [44256],

    [CY5 Total],

    [44287], [427051],

    [2016 Total1],

    [427361], [427671],

    [427951], [428261],

    [428561], [428871],

    [429171], [429481],

    [429791], [430091],

    [430401], [430701],

    [2017 Total1],

    [431011], [431321],

    [431601], [431911],

    [432211], [432521],

    [432821], [433131],

    [433441], [433741],

    [434051], [434351],

    [2018 Total1],

    [434661], [434971],

    [435251], [435561],

    [435861], [436171],

    [436471], [436781],

    [437091], [437391],

    [437701], [438001],

    [2019 Total1],

    [438311], [438621],

    [438911], [439221],

    [439521], [439831],

    [440131], [440441],

    [440751], [441051],

    [441361], [441661],

    [2020 Total1],

    [441971], [442281],

    [442561], [442871]

FROM

[Copy of Tracker_testing_TT - Copy.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [Project Data]);