topic Same expression returns different value in Straight Table in QlikView

Same expression returns different value in Straight Table

Fri, 25 Jan 2013 10:45:03 GMT

Hi Guys,

Please see the screenshot attached,

I have an expression which returns the number of calls taken by a specialist and devides it by the number of weekdays in the current selection.

The expression that calculates the number of weekdays is as follows:

sum({<specialist=>}_Work_Day_Flag)

For december 2012 this returns 21 weekdays ( the correct value)

but when I paste this exact same expression into the straight table it returns 14 (the number of unique dates that this specialist took calls)

I need it to return 21 in the straight table so it will calculate the correct value for ave number of calls per day.

The correct number of days is 21.

The correct average should be 1.0476 and NOT 1.5714 like it is returning.

Can anyone shed any light on why this is happening?

I am literally copyinig and pasting the expression from the text box.

Any help would be appreciated.

Re: Same expression returns different value in Straight Table

Gysbert_Wassenaar — Fri, 25 Jan 2013 11:17:13 GMT

Yes, in the chart the expression takes SpecialistID into account since it's a dimension of the chart. So there must be more specialists with a count. The total over all specialists should add up to 21. If you want the expression to return 21 for all specialists you can add the TOTAL keyword: sum(TOTAL _Work_Day_Flag). The set modifier is useless in the chart so you might as well leave it out.

Re: Same expression returns different value in Straight Table

Fri, 25 Jan 2013 11:21:05 GMT

Gysbert,

21 is the number of weekdays in the current date selection.

in December 2012 there were 21 weekdays (mon - fri)

I need the straight table to return 21 regardless of which specialist is selected.

In the current selection it returns 14 because the specialist selected had actioned calls on 14 distinct days.

For my reporting I need to calculate average calls per day.

So if a month is selected - the field should count ALL weekdays in that month regardless of how many calls were taken on those days.

Does this make more sense?

Re: Same expression returns different value in Straight Table

Gysbert_Wassenaar — Fri, 25 Jan 2013 11:27:40 GMT

It's probably easier to use a variable that uses the monthstart and monthend functions in the networkingdays function to calculate the number working days. Perhaps something like this:

vWorkDays = networkdays(monthstart(min(MyDate)),monthend(max(MyDate)))

This way you can select a period (or a date) and get the number of working days over the months in which the period falls.

You can then use the variable vWorkDays in your expressions.

Re: Same expression returns different value in Straight Table

Fri, 25 Jan 2013 11:35:32 GMT

This is a good idea Gysbert, but this also needs to be able to count workdays between any timespan selected,

eg: Workdays in 2012

workdays in a week

workdays between any selection of dates.

Re: Same expression returns different value in Straight Table

Fri, 25 Jan 2013 11:37:18 GMT

Can anybody explain me how to connect qlikview with bakends especially (sql server and oracle) step by step i am new to qlikview11

Re: Same expression returns different value in Straight Table

Gysbert_Wassenaar — Fri, 25 Jan 2013 12:04:52 GMT

Right, well as I said in your other discussion you could use calendar objects and variable to select a date range. You can use the same variables to calculate the number of working days in that range. I still think that is your best option.

Re: Same expression returns different value in Straight Table

Fri, 25 Jan 2013 12:23:21 GMT

Gysbert,

Thank you sooo much,

I did look at the link and didn't quite understand the application to this situation.

I didn't quite work it like the video but here is what i did:

In Document Properties -> Variables tab

selected variable and entered the expression

sum({<specialist=>}_Work_Day_Flag)

entered the variable name into the expressions on the Straight Table.

This works absolutely perfectly and I have learnt somehting about Variables thanks to you.

Once again thank you for your effort in helping

Regards

Re: Same expression returns different value in Straight Table

khadeer — Fri, 25 Jan 2013 12:24:00 GMT

First of all u have to create ODBC Connection.....

Controle Panel--> Administartive Tools---->Data Source(ODBC)

Here u have to create ODBC Connection, If u have already ODBC Connection,

Then in Qlikview---> Edit Script---> Database(ODBC)

Give username n password of the Ur Database , Then connection string will come, Then u can directly fetch the data from (Select) option next to the database