6 Replies Latest reply on Dec 29, 2018 4:57 PM by Praveenareddy Bandi

Count Based on Dates

Hi,

I need help on this calculation.

I want to count all the IDS where Date is between Begin of Week and End of Week  and display for each record as Count

Ex : for records Begin of Week 8/4/2018 and End of Week 8/10/2018 the count is showing as 10 instead of 25

Here 25 records have Date between 8/4/2018(Begin of Week) and 8/10/2018(End of Week)

My calculation for count is

{ Fixed [Begin of Week],[End of Week]: COUNTD(IIF((DATE([Date]))>= DATE([Begin of Week]) and DATE([Date]) <= DATE([End of Week]),[ID],NULL))}

Thanks

• 1. Re: Count Based on Dates

Hi Praveenareddy,

Not sure but find my approach as reference below based on "parameter" and stored in attached workbook version 10.5 located in the original thread.

1. P1. Begin of week Parameter

2. P2 End of week Parameter

3. D1. Display:

[P1. Begin of Week Parameter]<=[Date]

and [P2. End of Week Parameter]>=[Date]

4. Drag the required objects to the indicated location, filter D1. Display on True and show parameter controls,

Hope it helps.

Regards,

Norbert

• 2. Re: Count Based on Dates

but still my purpose is not served

Instead of showing 25, it is still showing 10 as I want to calculate the count for each record

Thanks

• 3. Re: Count Based on Dates

Hi Praveen,

The data set only has 10 records between 8/4 and 8/10. That is the reason why it's showing the sum as 10. I am not sure where we have the '25' in this dataset. The parameter approach above will fine for the scenario. If you still need the ID's on the display and have the sum along with it, then use an EXCLUDE command to exclude the ID's and find the sum of the number of records.

Let me know how it helps.

Regards

Vishnu

• 4. Re: Count Based on Dates

Vishnu,

Actually 25 is coming from count of all the IDs whose Date is between Begin of Week and End of Week

And here 25 records have Date between 8/4 to 8/10, Hence 25 should be displayed instead of 10

Exclude didn't help me too

Thanks.

• 5. Re: Count Based on Dates

Hi Praveen,

Thank you for clarifying the data. I have used a nested LOD for calculating the requested data. Please find the below screenshot.

Regards

Vishnu