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

Count Based on Dates

Praveenareddy Bandi Dec 28, 2018 12:55 PM

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

Please suggest?

Thanks

Book1.twbx 49.0 KB

1. Re: Count Based on Dates

Norbert Maijoor Dec 29, 2018 1:02 AM (in response to Praveenareddy Bandi)
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

begin end week nalmai_v10.5.twbx 56.4 KB
Like Show 0 Likes(0)

Actions
2. Re: Count Based on Dates

Praveenareddy Bandi Dec 29, 2018 6:51 AM (in response to Norbert Maijoor)

Thanks Norbert for your response.
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
Like Show 0 Likes(0)

Actions
3. Re: Count Based on Dates

Vishnu Sekhar Dec 29, 2018 9:14 AM (in response to Praveenareddy Bandi)

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
Like Show 0 Likes(0)

Actions
4. Re: Count Based on Dates

Praveenareddy Bandi Dec 29, 2018 11:29 AM (in response to Vishnu Sekhar)

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.
Like Show 0 Likes(0)

Actions
5. Re: Count Based on Dates

Vishnu Sekhar Dec 29, 2018 2:43 PM (in response to Praveenareddy Bandi)
Hi Praveen,

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

Please mark the answer as 'Helpful' if it resolved the issue. It will help other users.

Regards
Vishnu

Nested LOD.twbx 61.6 KB
Like Show 0 Likes(0)

Actions
6. Re: Count Based on Dates

Praveenareddy Bandi Dec 29, 2018 4:57 PM (in response to Vishnu Sekhar)

Thanks Vishnu for your response
My tableau is 10.5 version and unfortunately I cannot open your twbx in higher version.
But I'm expecting values for each week based on Begin of Week and End of Week which is dynamic and not static

Only these records in blue to display 25 (with Begin of week and End of week )

Thanks
Like Show 0 Likes(0)

Actions