How to Group by date, but need values from beginning to the group by date

Question & Answer
, ,
1

Hi,
I have one calculated field
sum(
count(
ifelse(
{subscription_date} < addDateTime(1,“MM”, truncDate(“MM”, {subscription_date})) AND
{removal_date} >= addDateTime(1,“MM”, truncDate(“MM”, {subscription_date})) OR
isNull({subscription_date})), id,
NULL
),
[{subscription_date} ASC]
)
)
if I do group by subscription_date(MONTH), it is only giving values of that particular month, how can I get values from that particular month and months before?

2

Have you tried getting rid of the partition by?

sum(
count(
ifelse(
{subscription_date} < addDateTime(1,“MM”, truncDate(“MM”, {subscription_date})) AND
{removal_date} >= addDateTime(1,“MM”, truncDate(“MM”, {subscription_date})) OR
isNull({subscription_date})), id,
NULL
),

)
)

2 Likes
3

Hi Max,
Thank you for your reply,
Let me explain
if I create a calculated field like this:

sum(
count(
ifelse(
{subscription_date} < addDateTime(1,“MM”, truncDate(“MM”, now())) AND
{removal_date} >= addDateTime(1,“MM”, truncDate(“MM”, now())) OR
isNull({subscription_date})), id,
NULL
),

)
)
while creating the visual I will get the count of current month.

But, what I’m looking for is to create a line chart which shows the counts per month group by subscription_date(MONTH).

4

Hi @Vishnudev

I believe I see what you are trying to do.

Have you tried running sum on your initial calculated field instead of sum?