Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This is my script:
Daily_Population:
LOAD
monthname([date_signed]) AS [Month Year],
Count(DISTINCT [PlayerI_D]) as CountofPlayers,
RangeSum(Count(DISTINCT [Player_ID]),peek(CumulativeCountofPlayers)) as CumulativeCountofPlayers
RESIDENT [alpha_fact_client_info]
GROUP BY monthname([date_signed]);
My end goal is something like this:
Month | Population | Total Population (Running)
Jan 2022 | 10 | 10
Feb 2022 | 5 | 15
Mar 2022 | 30 | 45
Apr 2022 | 50 | 95
It keeps throwing me an invalid expression error!
maybe this works for you?
Daily_Population:
LOAD *,
RangeSum(CountofPlayers,Peek(CumulativeCountofPlayers)) as CumulativeCountofPlayers;
LOAD MonthName(date_signed) as [Month Year],
Count(DISTINCT PlayerI_D) as CountofPlayers
RESIDENT [alpha_fact_client_info]
GROUP BY monthname([date_signed])
Order By date_signed;
Just do it in two steps - at the first load aggregating the values and within a second sorted resident-load you cumulate the results with rangesum() + peek().
- Marcus
maybe this works for you?
Daily_Population:
LOAD *,
RangeSum(CountofPlayers,Peek(CumulativeCountofPlayers)) as CumulativeCountofPlayers;
LOAD MonthName(date_signed) as [Month Year],
Count(DISTINCT PlayerI_D) as CountofPlayers
RESIDENT [alpha_fact_client_info]
GROUP BY monthname([date_signed])
Order By date_signed;
This worked! Thank you!