Previous Year Sales

[twinklea]

Hi,

I need the calculation of total sales for the previous year irrespective of the MonthYear dimension in a table.

Below is the scenario:

MonthYear	Sales	Previous Year Total Sales
Jan-18	300	1000
Feb-18	730	1000
Mar-18	400	1000
Apr-18	450	1000
Jan-17	100	1460
Feb-17	200	1460
Mar-17	300	1460
Apr-17	400	1460
Jan-16	320	0
Feb-16	180	0
Mar-16	460	0
Apr-16	500	0

Suppose for Jan2018 to Apr 2018 , it should calculate the sales from Jan 2017 to Apr 2017 and result as 1000 for all the 4 records and similar way for other years.

Please note that this output should also reflect filters from the dashboard.

Request the solution asap.

Regards,

Twinkle A

[twinklea]

The sample is the same as previous one. I am getting 2000 for all the records of 2018 year now instead of 1000.

[sunny_talwar]

I meant a qvf file... because I am not sure what exactly you have beyond what I see here... if I recreate... I am almost certain, I won't see this problem...

[twinklea]

I got the issue.. I had to remove the outer sum which is why it was getting doubled.

Now, your expression with Nodistinct function works fine for me.

Thank you so much.

[sunny_talwar]

good to know....

[somacdc]

I want to following result sir,

ELEMENT_ID	ACCOUNT_NUMBER	CASH_DEPOSIT	TARIFF_DEDUCTED	SLAB	CASH_DEPOSIT_ADJUSTED(follow the below caculation)	DIFF_TARRIF_ADJUST(TARIFF_DEDUCTED - CASH_DEPOSIT_ADJUSTED)	CASH_BALANCE= (CASH_BALANCE-TARIFF_DEDUCTED)
91	102935	10000	0	7	0	(0-0)=0	(1000-0)=1000
91	102935	0	4124	6	4124	(4124-4124)=0	(1000-4124)=5876
91	102935	0	4124	5	4124	(4124-4124)=0	(5876-4124)=1752
91	102935	0	4124	4	1752	(4124-1752)=2372	(1752-4124)=0
91	102935	0	4124	3	0	(4124-0)=4124	(0-4124)=0
91	102935	0	5124	2	0	(5124-0)=5124	(0-5124)=0
91	102935	0	5124	1	0	(5124-0)=5124	(0-5124)=0
92	102937	...	...	7	...	..	...
92	102937	...	...	6	...	..	...
92	102937	...	...	5	...	..	...
92	102937	...	...	4	...	..	...
92	102937	...	...	3	...	..	...
92	102937	...	...	2	...	..	...
92	102937	...	...	1	...	..	...
...	...	...	...	....	...	..	...
...	..
So on	So on	So on	So on

if(Cash_Balance>0) then
      if(Cash_Balance >= TARIFF_DEDUCTED) then
                 CASH_DEPOSIT_ADJUSTED := TARIFF_DEDUCTED;
      else
                CASH_DEPOSIT_ADJUSTED := TARIFF_DEDUCTED-abs(Cash_Balance-TARIFF_DEDUCTED);
      end if;
else
      CASH_DEPOSIT_ADJUSTED :=0;
end if;