topic Re: Set Analysis Expression in QlikView

Set Analysis Expression

gouthamkk — Fri, 28 Mar 2014 14:00:09 GMT

Hi Experts,

I am trying to get count of using below expression

count({<[Date Closed] > $(VInputDate)> , <Status={'Closed'}>, <Severity= {'High'}>} Status)

The above expression was working fine without the date comparison "[Date Closed] > $(VInputDate)"

Date Closed is the database filed which has random dates and VinputDate is a variable which changes dynamically. I know this is simple but am struggle to get the total count of the status which Vinputdate is < Date closed.

It would be great if some one could help

Thanks

Goutham

Message was edited by: Goutham Kandukuri

Re: Set Analysis Expression

Anonymous — Fri, 28 Mar 2014 14:04:34 GMT

try something like:

count({<[Date Closed] = {"> $(=VInputDate)"} , Status={'Closed'}, Severity= {'High'}} Status)

count({<[Date Closed] > {"$(=VInputDate)"} , Status={'Closed'}, Severity= {'High'}} Status)

Re: Set Analysis Expression

mreinders — Fri, 28 Mar 2014 14:05:31 GMT

Hello Goutham,

try if it helps:

Count({< [Date Closed] = {">$(VInputDate)"}, Status = {'Closed'}, Severity = {'High'} >} Status)

Greetings

Michael

Re: Set Analysis Expression

gouthamkk — Fri, 28 Mar 2014 14:06:56 GMT

Hi,

sorry it is not working, i am getting black values, Do i need to use total or aggr function since i am using this as expression in chart table.

Re: Set Analysis Expression

Fri, 28 Mar 2014 14:07:09 GMT

try below

count({<[Date Closed] = {"> $(=VInputDate)"} , Status={'Closed'}, Severity= {'High'} >} Status)

Vinay

Re: Set Analysis Expression

Anonymous — Fri, 28 Mar 2014 14:13:20 GMT

depends on how your setup is. count returns one value. so if you are trying to use it as a dimension, then you need aggr.

if it is an expression, it will depend on your dimensions in the straight table...can you take screenshots or upload your file with sample data?

ps: just fyi, use advanced editor on the top right side of this reply textbox to attach files. (link to attach will be at the bottom right corner of your reply box in advanced editor mode)

Re: Set Analysis Expression

gouthamkk — Fri, 28 Mar 2014 14:17:20 GMT

Hi I just added the table chart

Re: Set Analysis Expression

jerem1234 — Fri, 28 Mar 2014 14:19:10 GMT

Should be:

count({<[Date Closed] = {'>$(=VInputDate)'} , Status={'Closed'}, Severity= {'High'}>} Status)

like is stated in previous posts. I'm interested in the formula for VInputDate. How are you calculating this? Does this change for each [Date Closed]?

It might be that set analysis might not work in your situation, and must add an if statement.

Hope this helps!

Re: Set Analysis Expression

gouthamkk — Fri, 28 Mar 2014 14:19:40 GMT

Hi,

I tried you expression, i am getting all values 0

Re: Set Analysis Expression

gouthamkk — Fri, 28 Mar 2014 14:21:09 GMT

VInputdate is an Input variable entered randomly, but i did check the date formats, they are right

Re: Set Analysis Expression

Anonymous — Fri, 28 Mar 2014 14:23:08 GMT

so what is the expression for your variable? sample data/file will help a lot.

Re: Set Analysis Expression

gouthamkk — Fri, 28 Mar 2014 14:23:54 GMT

Hi Michael,

I tried your expression, getting all 0 in the table

Re: Set Analysis Expression

Fri, 28 Mar 2014 14:26:35 GMT

if VInputDate is variable then Remove = sign and use below

count({<[Date Closed] = {"> $(VInputDate)"} , Status={'Closed'}, Severity= {'High'} >} Status)

let see my expression which I am using in my application will help you

if(vFromDate >= Sdate1,sum({<T_Date= {'>=$(vFromDate) <=$(vToDate)'} >}Tday),0)

Re: Set Analysis Expression

jonathandienst — Fri, 28 Mar 2014 14:31:42 GMT

When doing date comparisons, the date format of the two expressions must be the same. How are you entering VInputdate? Check the format of [Date Closed] (easiest way - create a list box and scan through the values to confirm the format and that they are all formatted the same way. If you need to create a specific format:

count({<[Date Closed] = {"> $(=Date(VInputDate, 'DD-MM-YYYY'))"} , Status={'Closed'}, Severity= {'High'}} Status)

(this example assumes Date Closed is in DD-MM-YYYY format)

HTH

Jonathan

Re: Set Analysis Expression

stevedark — Fri, 28 Mar 2014 14:32:23 GMT

You need to be very careful with matching date formats in Set Analysis, your VInputDate variable may need a Date function in it, eg:

=Date(today(), 'DD MMM YYYY')

Where the date format specifier exactly matches the natural format of the Date Closed field. You could apply a Date function to your load script to ensure you know what the date format is of that field.

Re: Set Analysis Expression

gouthamkk — Fri, 28 Mar 2014 14:48:46 GMT

I did add sample data

Re: Set Analysis Expression

gouthamkk — Fri, 28 Mar 2014 14:50:24 GMT

Hi Steve,

I did check the format, as requested but i am still getting null values, FYI i did attach sample data

Re: Set Analysis Expression

gouthamkk — Fri, 28 Mar 2014 14:51:36 GMT

Hi Jonathan,

I did try, getting null values just in case, i attached sample data

Re: Set Analysis Expression

Fri, 28 Mar 2014 15:31:17 GMT

See attached!

Vinay