5 Replies Latest reply on Apr 4, 2017 6:05 PM by Vandana Samtani

Counts With date conditions

Hi All,

I am working on a data set to figure out top shows  that the user watched in last 7 days and will be refreshed every day.

Can you please give any suggestions?

Thank you,

Vandana

Below is the data set -

 Show Name User Date show 1 1 3/2/2017 Show 2 1 3/1/2017 show 1 4 2/28/2017 Show 3 5 2/27/2017 Show 5 1 2/27/2017 show 1 2 2/26/2017 Show 2 1 2/25/2017 show 1 3 3/1/2017 Show 3 1 2/24/2017 Show 5 2 2/25/2017 show 1 4 2/23/2017 Show 2 1 2/22/2017 show 1 5 2/21/2017 Show 3 1 2/26/2017 Show 5 1 3/1/2017 Show 4 2 3/2/2017 Show 6 2 3/3/2017
• 1. Re: Counts With date conditions

Hi, Vandana,

1. create a calc field that writes (dim)

T7D_user

IIF(datediff('day',[Date],today())<=7,user,null)

2. create a calc field that writes

#of T7D_user

countd(T7D_user)

3. place ShowName dimension and #of T7D_user measure and sort in desc. order.

Hope this helped.

Thanks,

Mia

• 2. Re: Counts With date conditions

Hi Vandana,

Thanks

Deepak

1 of 1 people found this helpful
• 3. Re: Counts With date conditions

Hi Mia,

Quick Quesiton on Date Differrence - I am calculating counts 30 days prior if a user selects any date from the parameter.

For e.g. - User selected 03/05/15 then the caculation should give counts for 30 days prior from 03/05/2015

This is what I am using for Past 30 days calculation

IIF(DATEDIFF('day',[Select Date],[Subscription Date]-30) <=30,[External User Id],null)

Then Count(Past 30 Days)

The business requirement is to calculate counts for prior 30 days from the date user selects from parameters.

Vandana

• 4. Re: Counts With date conditions

Hi, Vandana

I am not quite understanding what you're asking is exactly. You can use the same algorithm I gave you, just add parameter(UserSelection) and write,

1. create a calc field that writes (dim)

T7D_user

IIF(datediff('day',[Date],Param_UserSelection)<=30,user,null)

2. create a calc field that writes

#of T7D_user

countd(T7D_user)

3. place ShowName dimension and #of T7D_user measure and sort in desc. order.

Hope I understood your question correctly.

Thank you.

• 5. Re: Counts With date conditions

Thanks Mia!

I have the same logic that you recommended. Just replaced the date condition with parameter and it will give the counts for last 30 days.

Thanks again for all your help,

Vandana