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

**URL:** <https://community.amazonquick.com/t/how-to-group-by-date-but-need-values-from-beginning-to-the-group-by-date/12998>\
**Category:** Q&A\
**Tags:** visualization, pivot-table, calculations, quick-sight\
**Created:** [May 3, 2023, 12:42pm UTC](https://community.amazonquick.com/t/how-to-group-by-date-but-need-values-from-beginning-to-the-group-by-date/12998 "2023-05-03T12:42:35Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vishnudev](https://avatars.discourse-cdn.com/v4/letter/v/e9a140/32.png) [@Vishnudev](https://community.amazonquick.com/u/Vishnudev)\
**Post date:** [May 3, 2023, 12:42pm UTC](https://community.amazonquick.com/t/how-to-group-by-date-but-need-values-from-beginning-to-the-group-by-date/12998/1 "2023-05-03T12:42:35Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Max](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/max/32/2288_2.png) [@Max](https://community.amazonquick.com/u/Max)\
**Post date:** [May 3, 2023, 8:15pm UTC](https://community.amazonquick.com/t/how-to-group-by-date-but-need-values-from-beginning-to-the-group-by-date/12998/2 "2023-05-03T20:15:22Z")

</div>

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  
),  
  
)  
)

---

<div class="post-metadata">

**Author:** ![Vishnudev](https://avatars.discourse-cdn.com/v4/letter/v/e9a140/32.png) [@Vishnudev](https://community.amazonquick.com/u/Vishnudev)\
**Post date:** [May 4, 2023, 12:04pm UTC](https://community.amazonquick.com/t/how-to-group-by-date-but-need-values-from-beginning-to-the-group-by-date/12998/3 "2023-05-04T12:04:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Max](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/max/32/2288_2.png) [@Max](https://community.amazonquick.com/u/Max)\
**Post date:** [May 16, 2023, 8:56pm UTC](https://community.amazonquick.com/t/how-to-group-by-date-but-need-values-from-beginning-to-the-group-by-date/12998/4 "2023-05-16T20:56:29Z")

</div>

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?

> **[runningSum - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/runningSum-function.html)**
>
> The runningSum function calculates a running sum for a measure based on the specified dimensions and sort orders.