topic Re: Need help in expression in QlikView

Need help in expression

Anonymous — Tue, 11 Aug 2015 13:07:06 GMT

Hi,

I had written one expression, but it is showing invalid data. in expression it is ok, but some where it is going wrong.

Pls see the screen shot and help me on this.

Regards,

Kumar

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 13:12:03 GMT

I suppose that Date is a built-in function and requires some parameter

did you mean [Time Full Date]={"$.....)??

what do you mean in Expression okay??

Re: Need help in expression

jonathandienst — Tue, 11 Aug 2015 13:13:08 GMT

Its a lot easier if you post the text rather than an image, but you need:

[Site Visit Hit Source] -= {5, 7, 8, 9}, ....

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 13:17:45 GMT

Hi,

Pls find the expression.

=count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"}, [Site Visit Post Page Event] = {0}, Date={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)"}>} [Visitor ID])

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 13:22:03 GMT

Hi Kumar,

what you want to achieve with this piece of code :

Date={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)"}

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 13:24:11 GMT

Hi,

I am getting '0' for all dates. Actually before Include [Time Full Date]={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)"} this one in my set analysis. I used to get values. After including this I am getting 0's

Before

=count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"}, [Site Visit Post Page Event] = {0},[Time Full Date]={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)"}>} [Visitor ID])

After

Pls help me why it is behaving like this.

Regards,

Kumar

Re: Need help in expression

Tue, 11 Aug 2015 13:26:33 GMT

Hi Try This

set vmaxdate= max(date([TimeFulldate]))

set vdate1= addmonths($(vmaxdate),-1)

=count(DISTINCT{$<[Site Visit Hit Source]-={"5,7,8,9"}, [Site Visit Post Page Event] = {0}, Date={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)"}>} [Visitor ID])

count(DISTINCT{$<[Site Visit Hit Source]-={"5,7,8,9"}, [Site Visit Post Page Event] = {0}, Date={"$(vdate1)"}>} [Visitor ID])

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 13:27:20 GMT

Hi,

Actually, I am trying to get last 31 days data from max(date). So i include [Time Full Date]={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)"} this one in my set analysis.

See original expression.

count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"}, [Site Visit Post Page Event] = {0},[Time Full Date]={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)"}>} [Visitor ID])

Regards,

Kumar

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 13:27:23 GMT

your first part in Expression seems to be incorrect

see jonathans reply

what do you want achieve with: [Site Visit Hit Source]={"<>5,7,8,9"}

all except value 5,7,8,9? then use jonathans solution

Re: Need help in expression

sunny_talwar — Tue, 11 Aug 2015 13:29:17 GMT

Try this:

=Count(DISTINCT{$<[Site Visit Hit Source] -= {'5', '7' ,'8' ,'9'}, [Site Visit Post Page Event] = {0},[Time Full Date]={"$(=max(Date([Time Full Date],'MM/DD/YYYY'))-30)"}>} [Visitor ID])

=Count(DISTINCT{$<[Site Visit Hit Source] -= {'5', '7' ,'8' ,'9'}, [Site Visit Post Page Event] = {0},[Time Full Date]={"$(=Date(Max([Time Full Date]) - 30,'MM/DD/YYYY'))"}>} [Visitor ID])

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 13:46:46 GMT

Hi Sunny,

As u suggested i tried, but still i am able to see 0's only.

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 13:47:37 GMT

Hi Manjunath,

I included variables and tried, but no use.

Re: Need help in expression

sunny_talwar — Tue, 11 Aug 2015 13:49:39 GMT

Did you try this one?

=Count(DISTINCT{$<[Site Visit Hit Source] -= {'5', '7' ,'8' ,'9'}, [Site Visit Post Page Event] = {0},[Time Full Date]={"$(=Date(Max([Time Full Date]) - 30,'MM/DD/YYYY'))"}>} [Visitor ID])

Is Time Full Date a date field or TimeStamp field?? If it is TimeStamp then your expression will most likely not going to work.

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 13:54:05 GMT

I would step by step

provide an expression just with "sum([Visitor ID), any results?

next provide listbox with [Site Visit Hit Source] and select all values other than 5,7,8,9

next provide listbox with [Site Visit Post Page Event] and select 0, any results

finally provide listbox with ([Time Full Date]) and select last 30 days

if you still get a result, then search for error in expression

if you didnot get a result, than check your data

Re: Need help in expression

jonathandienst — Tue, 11 Aug 2015 13:56:30 GMT

jontydkpi wrote:

[Site Visit Hit Source] -= {5, 7, 8, 9}, ....

I have already given you the answer, did you try this?

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 14:12:53 GMT

Yes, it is time stamp only. Do we have any alternate solution to achieve this one.

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 14:13:14 GMT

data is there.

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 14:13:46 GMT

Hi Jonathan,,

I tried already, but it not working.

Re: Need help in expression

Anonymous — Tue, 11 Aug 2015 14:22:49 GMT

hi,

please split timestamp field to date and time fields and use date field in expression

Re: Need help in expression

Gabriel — Tue, 11 Aug 2015 14:28:50 GMT

Hi,

Your DISTINCT should be before [Visitor ID]