topic Variable in set analysis in QlikView

Variable in set analysis

Sat, 21 Jun 2014 10:43:04 GMT

Hi All,

I have a variable:

Date(AddMonths(min({<[Fcst Version]={"$(=Maxstring(Fcst_Num))"}>}[Fcst Month]),-1),'MMM-YY')

This determines the Minimum month of the maxstring of the fcst Version.

Now I have a set analysis that I need to include the variable.

Sum({<MonthYear={insert variable here}>}Fcst Qty)

How can I apply that?

My current set analysis does not work:

Sum({<MonthYear={$vMinFcstMonth}>} Fcst Qty)

Thanks

Re: Variable in set analysis

sundarakumar — Sat, 21 Jun 2014 12:26:43 GMT

try using

sum({<[Month Year]={'$(=vMinFcstMonth)'}>}[Fcst Qty])

pls check first if the variable is returning the required value with a text box

-Sundar

Re: Variable in set analysis

sundarakumar — Sat, 21 Jun 2014 12:30:37 GMT

if this doesn't work,

put an equal sign before the variable in variable overview

check if it is returning the year month intended by putting it in a text box

if it is ok

use expression as

sum({<[Month Year]={$(vMinFcstMonth)}>}[Fcst Qty])

this should work.

-Sudnar

Re: Variable in set analysis

Sat, 21 Jun 2014 13:36:31 GMT

Yes this one works.. My other question is how to create a comparison between a variable in a set analysis.

ex: vMinMonth = Apr-14

sum({<[Month Year]={">=$(=Min([Fcst Month]))<=$($(vMinMonth))"}>}[Fcst Qty])

// what I want sum({<[Month Year]=Mar-14<=Apr-14>}

on my part this does not produce the result I wanted

Re: Variable in set analysis

sundarakumar — Sun, 22 Jun 2014 04:47:23 GMT

I would prefer to change the data to its number format when u want to implement such time ranges.

Because interpreting the number is faster and easier. though this can be done with the time fields too.

num(date) would return the number format of the date. have this as a field in ur back end. use this new field in ur set analysis. for eg if u want to see the data of last one month u would use it as

sum({<date_num={>vlastmonthdate,<vtoday}>}sales)

create variable like vtoday=num(today()) and vlastmonthdate=num(today()-30).

Like this u can create what ever time range u wish to filter.

Pls specify the business rule of what time range u need so that we can help u in the same.

Hope this helps.

Regards,

Sundar