topic Re: How to using the Condition function with the Set Analysis in QlikView

How to using the Condition function with the Set Analysis

tabisen1 — Thu, 06 Feb 2014 09:16:34 GMT

Hi,

Can Any one tell me that how can i use the below function in the Set Analysis the coding I am using as below.

Prorate Coding for monthly target = if(date(vToday)=date(vToday) and date(vToday)<=monthend,sum((Del_Target)/Del_No_Day)*(day(vToday)-1),sum((Del_Target)/Del_No_Day)*day(monthend))

Actual sales Quantity = sum(QTY)

Below set is working but it capture the total monthly target rather then the prorate target.

Set Analysis= Count({$<[Parent Code]={'=sum(QTY)/(Monthly_Target)'}>} DISTINCT [Parent Code])

# Replacing the {'=sum(QTY)/(Del_Target)'} by = sum(QTY) / if(date(vToday)=date(vToday) and date(vToday)<=monthend,sum((Del_Target)/Del_No_Day)*(day(vToday)-1),sum((Del_Target)/Del_No_Day)*day(monthend))

Please suggest how to use the Prorate Target rather then Monthly_ Target.

Re: How to using the Condition function with the Set Analysis

Thu, 06 Feb 2014 09:21:34 GMT

INsert the cndition function in a variable, and use the variale in set analysis.

Re: How to using the Condition function with the Set Analysis

tabisen1 — Thu, 06 Feb 2014 09:37:17 GMT

Dear Mario,

In the above expression i am using the Variable i.e. vToday.

Table fields

Del_No_Day

Del_Target

QTY

Can u explain me in details how to create the prorate variable

Re: How to using the Condition function with the Set Analysis

Thu, 06 Feb 2014 09:45:14 GMT

vCondition=

sum(QTY) / if(date(vToday)=date(vToday) and date(vToday)<=monthend,sum((Del_Target)/Del_No_Day)*(day(vToday)-1),sum((Del_Target)/Del_No_Day)*day(monthend))

and Replacing the {'=sum(QTY)/(Del_Target)'} by {$(vCondition)}

Re: How to using the Condition function with the Set Analysis

tabisen1 — Thu, 06 Feb 2014 10:47:41 GMT

Mario,

I am getting the below error while Loading.

Aggregation expressions not allowed in GROUP BY clause

Let VCondition= sum(QTY) / if(date(vToday)=date(vToday) and date(vToday)<=monthend,

sum((Del_Target)/Del_No_Day)*(day(vToday)-1),sum((Del_Target)/Del_No_Day)*day(monthend))

their are two table in one table the Monthly Target and other table the Quantity.

to join two table i have created a key.

Re: How to using the Condition function with the Set Analysis

dirk_konings — Thu, 06 Feb 2014 10:52:43 GMT

you can't use this in scripting

for scripting each user field should be in the same table.

Why do you wan't to do this in scripting and not via an expression in a straight table ?

Dirk

Re: How to using the Condition function with the Set Analysis

Thu, 06 Feb 2014 10:55:12 GMT

My instructions are not for scripting, but for use in presentation...in scripting you can't use it!