Getting different result for the same set analysis expression

[trdandamudi]

I have a set analysis expression that I am using in pivot table and they are as below:

Dimensions:

ID

UsedDate

Time

Expressions:

=Sum(For_mms) / count({<Source={'XYZ'}>} DISTINCT [UsedDate])  <-------------------- I am getting 43 in the pivot

Text Box:

=Sum(For_mms) / count({<Source={'XYZ'}>} DISTINCT [UsedDate])   <-------------------- I am getting 48 in the text box

When I am using the same set analysis expression in the pivot and text box I get different results ? What am I missing here ?

Thanks

[Gysbert_Wassenaar]

Why are you looking at the 5am column and only for ID 590000? You don't filter for those values in your count expression. You're comparing apples and oranges.

[petter]

You have simply forgotten to take into account that the Text Box does not select or qualify the expression in any way to just use 5 AM. The Pivot Table has the time as a dimension and it will calculate the expression for each time as separate columns.

Just click in the Time list box on the 5 AM and you will see that 48 = 48.

[trdandamudi]

I was pointing out the 5 am and ID 590000 as an example. Let me see if I can explain this little better:

There are 3 ID's in the dashboard and when you count the number of Distinct "UsedeDate" I have 48 days.

So in the chart expression I need the same 48 days regardless of the Time to calculate for each ID. Meaning no matter what ID I select or not select it should only take 48 days into consideration. Hope this is clear...

Thank you for your time.

[trdandamudi]

Yes, the pivot table has the time dimension and it is calculating for each time as separate column. I don't want to take Time into the account. For example ID 590000 and Time 5am has only 43 "UsedeDate"  records. But if you disregard Time 5am then I have 48 records for "UsedeDate".. How can i put this logic in the expression ?

Thank you for your time...

[trdandamudi]

Avinash, i am not getting the correct results.

For example ID 590000 and Time= 5am has only 43 "UsedeDate"  records. But if you disregard Time= 5am then I have 48 records for "UsedeDate"..  i want  to disregard the time and get the 48 days. How can i put this logic in the expression ? I modified the expression as below:

=Aggr(Sum(For_mms) / count({<Source={'XYZ'},Time=>} DISTINCT [UsedeDate]),ID,UsedeDate,Time) but I am getting zeros. I don't think it should Aggr because then it will take 43 days only instead of 48 days. Hope I am clear.

Thanks

[petter]

To disregard selections you can put in Time= to disregard any selections on the Time field. For instance like this:

Count( { < Source={'XYZ'}, Type={'B'}, Time= ,ID= ,UsedeDate= > } DISTINCT [UsedeDate])

Here you will always take into the calculation Source and Type with the specific values and Time, ID and UsedeDate without any particularly selected values - which will be all values of these three fields....

[trdandamudi]

I changed the expression as below:

=IF(Dimensionality()= 2, Num(Sum(For_mms) / Aggr(count({<Source={'XYZ'},Time=>} DISTINCT [UsedDate]),ID),'#,##0.0'),Num(SUM(For_mms),'#,##0'))

So using the above expression I am getting the correct result. But the Average is showing only for the first column (5am) at the bottom. Remaining columns are showing a dash. How can I show the averages for the other columns also at the bottom

Date           5am 6am 7am 8am 9am 10am 11am 12pm etc..

12/1/2015    10    10     5

12/2/2015     20   10     5

Average:       15    --     --

Total    :        30    20   10

Thank you