topic Re: Previous Year Sales in App Development

Previous Year Sales

twinklea — Wed, 05 Jun 2019 08:15:23 GMT

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

Re: Previous Year Sales

martinpohl — Wed, 05 Jun 2019 08:21:33 GMT

Hi,

load your datas, add a field Year.

then add script:

YearSum:

load

Year,

sum(Sales) as YearSum

resident YourData

group by Year;

left join (YourData) load

Year+1 as Year

YearSum as "Previous Year Total Sales"

resident YearSum;

drop table YearSum;

Hope this helps,

regards

Re: Previous Year Sales

twinklea — Wed, 05 Jun 2019 09:45:24 GMT

Hi Martin,

I will try this solution and get back but i was also looking for this to be achieved using set analysis with sum and aggr function. I was not successful but if anyone can provide the solution with set analysis, it will be useful performance wise too.

Regards,

Twinkle A

Re: Previous Year Sales

twinklea — Wed, 05 Jun 2019 10:00:40 GMT

Hi Martin,

The script solution mentioned is not working correctly.

Regards,

Twinkle A

Re: Previous Year Sales

sunny_talwar — Wed, 05 Jun 2019 13:37:13 GMT

I think the best way to handle this would be to use The As Of Table

Re: Previous Year Sales

twinklea — Thu, 06 Jun 2019 04:16:05 GMT

Hi Sunny,

I tried using AsOfTable but not working out. May be I am missing out something here. Can you help with this example?

Re: Previous Year Sales

twinklea — Thu, 06 Jun 2019 06:01:35 GMT

Hi Sunny,

Is it possible for you to help me out with this issue of getting previous year total sales with the dimension of monthyear.

I have used the below SET ANALYSIS:

sum(aggr (sum({<Type={'Previous'},CalMonthYear=,Month_Temp=>} [Qty ()]),AsOfYear))

where AsOfYear & Month_Temp is my dimension

With this, I am getting the below output:

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

The total previous year value is coming but it showing only for the January row data which I want to be shown for rest of the months too the same values like 1000 should appear for all 4 months of year 2018.

Let me know please what i am missing here.

Thanks in advance.

Re: Previous Year Sales

sunny_talwar — Thu, 06 Jun 2019 10:58:30 GMT

You can try one of these

Sum(Aggr(NODISTINCT Sum({<Type = {'Previous'}, CalMonthYear, Month_Temp>} [Qty ()]), AsOfYear))

or this

Sum(TOTAL <AsOfYear>{<Type = {'Previous'}, CalMonthYear, Month_Temp>} [Qty ()])

Re: Previous Year Sales

twinklea — Thu, 06 Jun 2019 13:59:31 GMT

Hi Sunny,
The first expression with Nodistinct function is giving me the same values for all the months which i need but t is somehow showing the double values. For example, instead of 1000, I am getting 2000 for all the required records.
The second expression is drilling the result to the month level .
I guess the first expression needs to be more refined to get the correct output.

Let me know if you can suggest something on this.

Re: Previous Year Sales

sunny_talwar — Thu, 06 Jun 2019 14:15:33 GMT

Would you be able to provide a sample to check this out?

Re: Previous Year Sales

twinklea — Thu, 06 Jun 2019 14:19:00 GMT

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

Re: Previous Year Sales

sunny_talwar — Thu, 06 Jun 2019 14:20:11 GMT

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...

Re: Previous Year Sales

twinklea — Thu, 06 Jun 2019 14:26:36 GMT

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.

Re: Previous Year Sales

sunny_talwar — Thu, 06 Jun 2019 14:42:55 GMT

good to know....

Re: Previous Year Sales

somacdc — Fri, 16 Aug 2019 10:31:00 GMT

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;