topic Re: Getting different result for the same set analysis expression in QlikView

Getting different result for the same set analysis expression

trdandamudi — Thu, 24 Mar 2016 22:44:55 GMT

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

Dimensions:

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

Re: Getting different result for the same set analysis expression

ecolomer — Thu, 24 Mar 2016 22:55:39 GMT

Probably For_mms have more values possible in text box because aren't any dimension

Re: Getting different result for the same set analysis expression

trdandamudi — Thu, 24 Mar 2016 23:00:53 GMT

Thanks for the quick reply... How can I get the text box result into the pivot ? Should I need to re-write the expression in Pivot ?

Thanks

Re: Getting different result for the same set analysis expression

ecolomer — Thu, 24 Mar 2016 23:03:29 GMT

Try put only Sum(For_mms) in a text box and in a Pivot tabla and see it

Re: Getting different result for the same set analysis expression

trdandamudi — Thu, 24 Mar 2016 23:09:56 GMT

I am getting same result 40470.

Also tried the below expression in the pivot:

Sum(For_mms) / count(Total{<Source={'XYZ'}>} DISTINCT [UsedDate]) and I am getting correct results. I am getting the correct results when I select a individual id in the filter. If I clear the filter I am getting the wrong result ?

Re: Getting different result for the same set analysis expression

ecolomer — Thu, 24 Mar 2016 23:12:34 GMT

You can upload qv file?

Re: Getting different result for the same set analysis expression

trdandamudi — Thu, 24 Mar 2016 23:18:03 GMT

I do not have access to do that. Do you want any screen shots ?

Thanks

Re: Getting different result for the same set analysis expression

trdandamudi — Fri, 25 Mar 2016 00:33:09 GMT

I am able to put a sample dashboard so that you can take a look. I am also attaching a screen shot where I circled on three places.

The text box shows as 48 days. The Table - I shows 43 days for the same set analysis expression. By looking at the " Table - I " I am able to find out that the reason it is showing only 43 days instead of 48 days because there are 5 records in column (5 am) which are either null or zeros. Regardless of the the time I need to get 48 days because there are 48 records in the column "UsedeDate" with valid dates.

If you need any info please let me know. Thank you for your time.

Re: Getting different result for the same set analysis expression

avinashelite — Fri, 25 Mar 2016 07:56:03 GMT

Try this expression in the text box

Agg(Sum(For_mms) / count({<Source={'XYZ'}>} DISTINCT [UsedDate]),ID,UsedDate,Time)

Re: Getting different result for the same set analysis expression

Kushal_Chawda — Fri, 25 Mar 2016 08:04:54 GMT

try this

sum(Agg((Sum(For_mms) / count({<Source={'XYZ'}>} DISTINCT [UsedDate])),ID,UsedDate,Time))

Re: Getting different result for the same set analysis expression

Gysbert_Wassenaar — Fri, 25 Mar 2016 08:05:10 GMT

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.

Re: Getting different result for the same set analysis expression

petter — Fri, 25 Mar 2016 08:19:00 GMT

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.

Re: Getting different result for the same set analysis expression

trdandamudi — Fri, 25 Mar 2016 11:31:52 GMT

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.

Re: Getting different result for the same set analysis expression

trdandamudi — Fri, 25 Mar 2016 11:37:00 GMT

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

Re: Getting different result for the same set analysis expression

trdandamudi — Fri, 25 Mar 2016 11:49:16 GMT

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

Re: Getting different result for the same set analysis expression

petter — Fri, 25 Mar 2016 11:59:40 GMT

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

Re: Getting different result for the same set analysis expression

trdandamudi — Fri, 25 Mar 2016 12:52:09 GMT

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