Click here to Skip to main content
14,976,187 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have table from column [report date] to total, how to get [previous week data] and Gap as shown in table.

Previous Week Data = last week total value
Gap=Total-Previous week data.



Report Date	Category	Items	Partners	Sum of FQ3	Sum of FQ4	Total Previous week data	Gap
     4/15/2021	    ABC	        PRITB	PRT JP	      51797	      22782	     74579	    70289	      4290
     4/8/2021	    ABC	        PRITB	PRT JP	      47507	      22782	     70289	    0	        70289
     4/15/2021	    ABC	        PRITB	PRT PH	      325850	  330495	 656345	    655650	    695
     4/8/2021	    ABC	        PRITB	PRT PH	      325155	  330495	 655650	    0	           655650
     4/15/2021	    ABC	        PRITB	PRT VN	      1306246	  1688340	 2994586	2985300	    9286
     4/8/2021	    ABC	       PRITB	PRT VN	      1296960	  1688340	 2985300	0	        2985300


What I have tried:

I tried with pivot table but I'm not getting the expected results.
Posted
Comments
Gerry Schmitz 2-May-21 12:38pm
   
Create a separate query to calculate the previous week summaries (compute the current key); then join them with the current week summaries. Calculate the "gap".
Member 14936854 3-May-21 12:05pm
   
could you please help me with the query
CHill60 4-May-21 8:37am
   
Share the data that is in your table that would give the expected results you show. Also share the code that you tried and how the results were not as expected

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900