Substract data pivot from different filters

  • Hello Guys,


    I have a question,


    I have a pivottable with data (only numbers as values). In this pivot there is a report filter which filters current month and last month. Is it possible for the pivot to show the data as a difference between current month and this month and if so where to start with the vba code to let it substract from each other?

  • Re: Substract data pivot from different filters


    Quote

    difference between current month and this month


    Simple. Zero.
    Or do you mean difference between the first date of the current month and today
    vs the same period last month?
    Or do you check only at the end of each month?
    Do you account for different length of months and number of weekends
    by using custom calendars?

  • Re: Substract data pivot from different filters


    Sorry, my bad,


    I meant end of this month and and of last month.


    So those 2 are in the filter (so i can choose between the two). data in rows and columns is the same for both months. However I want to see the difference between the two months for those figures.

  • Re: Substract data pivot from different filters


    almost got it. Only thing that i need to know is how to get to a certain column of data.


    Say I have 3 columns which are "scenarios"


    if I want to sum up the data from one of those columns, how would I do that? i was thinking the lines of:



    Code
    Dim sumvalues As Double
    
    
    sumvalues = 0
    
    
    For Each xitem In Sheets(2).PivotTables(1).PivotFields("scenario").PivotItems("2")
        sumvalues = sumvalues + xitem
    Next


    But I cannot get it to work. any ideas?


    EDIT: how I read this is that pivottable1 has a pivotfield called scenarios. One of those scenarios is scenario 2. For each datapoint in scenario 2 add the data to a variable called sumvalues. Am I correct?

  • Re: Substract data pivot from different filters


    Sorry Eureka,


    Would you mind to attach a simple workbook with your expected results? (I'm not good in Pivot Table :()


    Let see if we can find out the solution.

    Best Regards,


    Tony


    [SIZE=1]A Peaceful Mine Is A Blessing[/SIZE]

  • Re: Substract data pivot from different filters


    Go it to work. Maybe not winning the beauty award but it works :)


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!