topic Set analysis with IF statement in QlikView

Set analysis with IF statement

Mon, 22 Sep 2014 11:27:55 GMT

Hi All,

I am creating drill down reports, there are three level of reports, when the user select the paticular metrics(Count of ID) in the first report, he will able to see the next level details associated with those selected IDs. so i have used Inline table(field Name Metricslable) to link to different metrics shown in the first report.

I have used a expression like this:

if(Metricslable='metric1',count({<Dim1={'aa'},year=,month=,ID=>}ID),

if(Metricslable=''metric2',count({<Dim1={bb},year=,month=,ID=>}ID))

I want to exclude the some filter selection, Year and month filter exclusion is working fine, but my ID slection is not workinfg fine.

Re: Set analysis with IF statement

anbu1984 — Mon, 22 Sep 2014 11:32:52 GMT

Can you post sample qvw

Re: Set analysis with IF statement

avinashelite — Mon, 22 Sep 2014 11:36:13 GMT

Hi Sampath,

Please post your app and the exact requirement

Re: Set analysis with IF statement

senpradip007 — Mon, 22 Sep 2014 11:37:57 GMT

Try like:

count({<Metricslable={'metric1'}, Dim1={'aa'},year=,month=,ID=> +

<Metricslable={'metric2'}, Dim1={'bb'},year=,month=,ID=>} ID)

Re: Set analysis with IF statement

PrashantSangle — Mon, 22 Sep 2014 11:43:38 GMT

Hi,

When you nullify ID field in your set analysis then it will give you wrong output.

Change your expression to

Count({<Metricslable={'metric1'}, Dim1={'aa'},year=,month=>}ID)

Count(<Metricslable={'metric2'}, Dim1={'bb'},year=,month=>} ID)

Regards

Re: Set analysis with IF statement

jonathandienst — Mon, 22 Sep 2014 12:17:38 GMT

Are you trying to create drill-down/rollup functionality? Something like Year | Month | Date - so that the chart displays Year by default; if you select a year it shows the data by months in the selected year; and if you select a month, it shows data by Date in that month?

If so, rather use QV built in functionality using drill groups rather than building complex logic, and use the drill group as the dimension in the chart. Check out groups and drill groups in the manual.

Your solution would look something like this:

Eg drill group grYMD - year, month, date

Use grYMD as the dimension, and something like Count(ID) as the expression

(Adapt to your specific requirements)

HTH

Jonathan