Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have problem. below is sample aggregated data but I want transaction data in dashboard.
Data:
LOAD
If(RecNo()<=6,'India','Japan') as Country,
Date1,
Date2
INLINE [
Date1, Date2, Sales
201701, 201611, 15
201701, 201612, 25
201701, 201701, 35
201702, 201612, 45
201702, 201701, 55
201702, 201702, 95
201701, 201611, 10
201701, 201612, 20
201701, 201701, 30
201702, 201612, 40
201702, 201701, 50
201702, 201702, 90
];
I am expecting that always one value selected in RD. Below pivot table is expected(Always one value selected in Date1).
Date1
Country 201701 201702
India 25 65
Japan 20 60
Explanation : 25 for 201701 for India is average of 15,25,35.
20 for 201701 for Japan is average of 10,20,30.
But this is aggregated data I have day level data I want transaction data in dashboard. Don't want to aggregate in script.
This means I have like 50 to 500 records for each month for Date2 I have shown you aggregated data here to practice but it transaction data.
Thanks
Swathi
Hi Swathi, to do the aggregation you can use an Aggr() that aggregates the value before doing the AVG:
Avg(Aggr(Sum(Sales), Date2))
Hi Ruben,
Thanks for replying. I tried but it shows correct for first yearmonth i.e. for 201701 but for 201702 it is showning only for max month only.
I tried Avg({<Date1>}Aggr(Sum({<Date1>}Sales),Date2,Country))
Regards
Swathi
Hi Swathi, I'm not sure of what you are trying, maybe adding Country to the aggr?
Avg(Aggr(Sum(Sales), Date2, Country))
PFA.
You may want to look at this example. It has multiple forecasting methods shown, including a rolling 3 month average.