Calculate percentage difference between dates with formula

[chilly_bang]

I have a table, like this:

+------------+--------------+--------+
| Week       | Search query | Clicks |
+------------+--------------+--------+
| 2020-11-09 | keyword 1    | 3      |
+------------+--------------+--------+
| 2020-11-16 | keyword 1    | 3      |
+------------+--------------+--------+
| 2020-11-09 | keyword 2    | 7      |
+------------+--------------+--------+
| 2020-11-16 | keyword 2    | 14     |
+------------+--------------+--------+

How can i calculate the percentage difference of clicks between the latest and earliest date with a formula?

Anyhow like this: IF B3=(B3-1) & A3>(A3-1) → iferror(100-(c3*100/(C3-1));0)

I've tried it with pivot - it works, but after data refresh my pivot gets broken.

 

[chilly_bang]

For example, in the first two records for "abmahnschutz", how do you get to "100" from those two records?

Not 100 - but 50.

This is the original:

+------------+-----------------------------------------------------+--------+
| Week       | Search query                                        | Clicks |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | abmahnschutz                                        | 4      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | abmahnschutz                                        | 8      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | abmahnschutz versicherung                           | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | abmahn versicherung                                 | 3      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | ambulante zusatzversicherung ohne gesundheitsfragen | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | ambulante zusatzversicherung ohne wartezeit         | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | app drive x                                         | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | autoversicherung                                    | 6      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | autoversicherung                                    | 6      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | autoversicherungen                                  | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | bauzeitversicherung                                 | 6      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | beamte altersvorsorge                               | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | beitragsentlastung                                  | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | beitragsentlastungstarif                            | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | beitragsentlastungstarif                            | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | betriebsunterbrechungsversicherung                  | 3      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | betriebsunterbrechungsversicherung                  | 10     |
+------------+-----------------------------------------------------+--------+

If i display the original table with dates as columns, so it looks like:

+-----------------------------------------------------+------------+------------+
| Search query                                        | 09.11.2020 | 16.11.2020 |
+-----------------------------------------------------+------------+------------+
| abmahn versicherung                                 |            | 3          |
+-----------------------------------------------------+------------+------------+
| abmahnschutz                                        | 4          | 8          |
+-----------------------------------------------------+------------+------------+
| abmahnschutz versicherung                           |            | 2          |
+-----------------------------------------------------+------------+------------+
| ambulante zusatzversicherung ohne gesundheitsfragen |            | 2          |
+-----------------------------------------------------+------------+------------+
| ambulante zusatzversicherung ohne wartezeit         | 2          |            |
+-----------------------------------------------------+------------+------------+
| app drive x                                         |            | 2          |
+-----------------------------------------------------+------------+------------+
| autoversicherung                                    | 6          | 6          |
+-----------------------------------------------------+------------+------------+
| autoversicherungen                                  | 2          |            |
+-----------------------------------------------------+------------+------------+
| bauzeitversicherung                                 | 6          |            |
+-----------------------------------------------------+------------+------------+
| beamte altersvorsorge                               |            | 2          |
+-----------------------------------------------------+------------+------------+
| beitragsentlastung                                  | 2          |            |
+-----------------------------------------------------+------------+------------+
| beitragsentlastungstarif                            | 2          | 2          |
+-----------------------------------------------------+------------+------------+
| betriebsunterbrechungsversicherung                  | 3          | 10         |
+-----------------------------------------------------+------------+------------+

So the date difference will be calculated with =IFERROR(100-(B2*100)/(C2);0) and the final result i want will look like:

+-------------------------------------------------------+--------------------------------+
| Search query                                          | Click Difference between dates |
+-------------------------------------------------------+--------------------------------+
| abmahn versicherung                                   | 100                            |
+-------------------------------------------------------+--------------------------------+
| abmahnschutz                                          | 50                             |
+-------------------------------------------------------+--------------------------------+
| abmahnschutz versicherung                             | 100                            |
+-------------------------------------------------------+--------------------------------+
| ambulante zusatzversicherung ohne   gesundheitsfragen | 100                            |
+-------------------------------------------------------+--------------------------------+
| ambulante zusatzversicherung ohne   wartezeit         | 0                              |
+-------------------------------------------------------+--------------------------------+
| app drive x                                           | 100                            |
+-------------------------------------------------------+--------------------------------+
| autoversicherung                                      | 0                              |
+-------------------------------------------------------+--------------------------------+
| autoversicherungen                                    | 0                              |
+-------------------------------------------------------+--------------------------------+
| bauzeitversicherung                                   | 0                              |
+-------------------------------------------------------+--------------------------------+
| beamte altersvorsorge                                 | 100                            |
+-------------------------------------------------------+--------------------------------+
| beitragsentlastung                                    | 0                              |
+-------------------------------------------------------+--------------------------------+
| beitragsentlastungstarif                              | 0                              |
+-------------------------------------------------------+--------------------------------+
| betriebsunterbrechungsversicherung                    | 70                             |
+-------------------------------------------------------+--------------------------------+

So how i come from the original table to the wanted result with a formula?

I'm very bad explainer. @Joe4 you are the hero on your trying to understand me.

 

[Joe4]

Do you only have the two dates?
If so, then why not just use the original data table, where you have two columns of dates?
Then, you could just put this formula in, which would only return a value if both date columns are populated for that row:

=IF(AND(B2<>"";C2<>"");100-(B2*100)/C2;"")

 

[chilly_bang]

Yes, there are always only two dates. Sure, your last method would be very handy but my original table doesn't have two columns for dates - in original table dates are all in the same column.

I add a file, where original table is located in the first tab, , the tab "Pivot" contains pivoted original data and calculation of click difference between dates, and the third tab "result" contains the table i want to achieve without pivot. Here is the example file.

 

[Joe4]

I do not know of a good approach without using VBA. I rarely use Pivots myself, and would tend to use Micosoft Access to do this (as I am much better with Microsoft Access queries than with Pivots).

In Excel, I would probably use VBA to change the structure of your data to look like the two-column date layout that you showed.
If that is an acceptable approach to you, let me know, and I can come up with VBA code do that.