Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've the below data in my excel source and I'm looking for expression to get sum of sales as per quarter.
Date | Sales |
01/01/2018 | 100 |
03/01/2018 | 200 |
10/01/2018 | 240 |
01/02/2018 | 120 |
03/02/2018 | 300 |
10/02/2018 | 400 |
01/04/2018 | 190 |
03/04/2018 | 432 |
10/04/2018 | 132 |
01/01/2019 | 567 |
03/01/2019 | 415 |
10/01/2019 | 690 |
I've edited script to get the quarter and looks like below.
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
I would like to see the output as below
Quarter | Sales |
Q1-2018 | 1360 |
Q2-2018 | 754 |
Q1-2019 | 1672 |
You would use set analysis to do that
Q1-2019
=Sum({<QuarterYear = {"$(=Dual('Q' & Ceil(Month(Max(Date))/3) & '-' & Year(Max(Date)), QuarterStart(Max(Date))))"}>}Sales)
Q2-2018
=Sum({<QuarterYear = {"$(=Dual('Q' & Ceil(Month(AddMonths(Max(Date), -9))/3) & '-' & Year(AddMonths(Max(Date), -9)), QuarterStart(AddMonths(Max(Date), -9))))"}>}Sales)
Q1-2018
=Sum({<QuarterYear = {"$(=Dual('Q' & Ceil(Month(AddMonths(Max(Date), -12))/3) & '-' & Year(AddMonths(Max(Date), -12)), QuarterStart(AddMonths(Max(Date), -12))))"}>}Sales)
hi
Please do the following
1: Using mapping load create a Quarter column in the same table/ Create master calendar to create Quarter column
In script:
If you want the output to be done in script use
Load Quarter, sum(Sales) as TotalSales
Resident table_name
group by Quarter;
In UI:
Table
Dimension -> Quarter
Measure -> Sum(Sales)
Thanks
Pradosh
Try something like this
Table: LOAD *, Dual('Q' & Ceil(Month(Date)/3) & '-' & Year(Date), QuarterStart(Date)) as QuarterYear; LOAD * INLINE [ Date, Sales 01/01/2018, 100 03/01/2018, 200 10/01/2018, 240 01/02/2018, 120 03/02/2018, 300 10/02/2018, 400 01/04/2018, 190 03/04/2018, 432 10/04/2018, 132 01/01/2019, 567 03/01/2019, 415 10/01/2019, 690 ];
and then create a chart with
Dimension
QuarterYear
Expression
Sum(Sales)
Thanks for that Sunny and it works perfectly the way I'm expecting. How can i write an expression in 'Text Object' to get quarter wise sum of sales? For example we know Q1 sales in 2018 is 1360 and i want 1360 in one text object and Q2 sales is 754 and i want 754 in another text object etc.....
You would use set analysis to do that
Q1-2019
=Sum({<QuarterYear = {"$(=Dual('Q' & Ceil(Month(Max(Date))/3) & '-' & Year(Max(Date)), QuarterStart(Max(Date))))"}>}Sales)
Q2-2018
=Sum({<QuarterYear = {"$(=Dual('Q' & Ceil(Month(AddMonths(Max(Date), -9))/3) & '-' & Year(AddMonths(Max(Date), -9)), QuarterStart(AddMonths(Max(Date), -9))))"}>}Sales)
Q1-2018
=Sum({<QuarterYear = {"$(=Dual('Q' & Ceil(Month(AddMonths(Max(Date), -12))/3) & '-' & Year(AddMonths(Max(Date), -12)), QuarterStart(AddMonths(Max(Date), -12))))"}>}Sales)
Hello Friend,
Please help me on my following post.
Thanks,
V.