topic Set Analysis - field1 to follow field2 values? in QlikView

Set Analysis - field1 to follow field2 values?

Sun, 26 Sep 2010 13:24:38 GMT

<body>Hi guys, I have a table that looks like shown below. The dimension is the month of the targets. The first column is the sum of target sales: Sum(SalesTargets.Quant) The second column SHOULD BE the sum of the sales: =Sum(QtyByBaseProduct) Since there is no relation between "Target Month" and "Sales Month", I am trying to change the expression in the second column in order to show "Sales Month" that correspond to the "Target Month" shown in the first column. In the second column, I am trying something like: =Sum({<[IvMonthNo] = {$(=[SalesTargets.STMonth])}>} QtyByBaseProduct) But that works only when I select a specific month from "Target Month". How can I work it around? Thanks in advance, Aldo. T.Month Target Sales 01 915 0 02 988 0 03 1061 0 ... 12 1354 0 <col width="76"></col> <col width="153"></col> <col width="142"></col> <tr height="20"> <td height="20" class="xl63" width="76"> </td> <td class="xl64" width="153"> </td> <td class="xl64" width="142"> </td> </tr> </body>

Set Analysis - field1 to follow field2 values?

Anonymous — Sun, 26 Sep 2010 14:55:55 GMT

Aldo,
Unless you have a valid reason not to do so, I recommend to use data model with one calendar, so both Target and Sales are related to the same Month.
As for the solution within your existing data model - I wouldn't provide an advice without seeing this data model.

Set Analysis - field1 to follow field2 values?

Sun, 26 Sep 2010 21:36:14 GMT

Hi Michael,

I tried building the data model as you suggest, but there are some limitations, so I have to work it around the way it is...

do you have any suggestion?

Aldo.

Set Analysis - field1 to follow field2 values?

Anonymous — Sun, 26 Sep 2010 22:41:12 GMT

Hi Aldo,

If you cannot change data model this way, one solution is to use expression for sales like this:
sum(if(SalesMonth=TargetMonth,Sales))

Depending on your data model (which I still don't know) it may work or not.

Another approach, if you can make a simple data model change, is to create a logcal island which contains one field Month. Next, use this Month as a dimension in the table, and expressions will be:
Sales: sum(if(SalesMonth=Month, Sales))
Target: sum(if(TargetMonth=Month, Target))

This is more flexible and should work with any data model, but performance may suffer when using logical islands. You can give it a try.

Still, I recommend to find a solution using my first advice, with one master calendar.

Set Analysis - field1 to follow field2 values?

Mon, 27 Sep 2010 07:35:52 GMT

Hi Michael,

First of all, Thanks for your help.

How do I create the logical island? something like below?

LOAD * INLINE [

CurrMonth, SalesMonth, TargetMonth

Month, IvMonth, SalesTargets.STMonth

];

Thanks,

Aldo.

Set Analysis - field1 to follow field2 values?

jonathandienst — Mon, 27 Sep 2010 07:47:12 GMT

Aldo

Assuming you have the minimum date of interest in variable vMinTrDate and the maximum in vMaxTrDate, then you could use something like this:


DATEISLAND:
LOAD TrDate,
 Month(TrDate) AS TrMonth,
 Year(TrDate) AS TrYear;
LOAD Date($(vMinTrDate) + RecNo() - 1) AS TrDate
AUTOGENERATE $(vMaxTrDate) - $(vMinTrDate) + 1;

Then dimension your chart with TrMonth.

Jonathan

Set Analysis - field1 to follow field2 values?

Mon, 27 Sep 2010 08:22:39 GMT

Hi Jonathan,

I am still trying to understand your suggestion.

In my Targets table, I have a field with the month, and one with the year:

SalesTarget.STMonth

SalesTargets.STYear

I do not have a date, but I can create it with makedate if needed.

What do you suggest me to do?

To create a min and max date? something like:

SET vMinTrDate = makedate(SalesTargets.STYear, Min(num#(SalesTargets.STMonth))

SET vMaxTrDate = makedate(SalesTargets.STYear, Max(num#(SalesTargets.STMonth))

DATEISLAND:
LOAD

TrDate,
Month(TrDate) AS TrMonth,
Year(TrDate) AS TrYear;
LOAD Date($(vMinTrDate) + RecNo() - 1) AS TrDate
AUTOGENERATE $(vMaxTrDate) - $(vMinTrDate) + 1;

Is that right? how this works?

Thanks in advance,

Aldo.

Set Analysis - field1 to follow field2 values?

jonathandienst — Mon, 27 Sep 2010 08:32:03 GMT

Aldo

I think you needto do this via a temporary table:


tempDates:
LOAD
 Min(MakeDate(SalesTargets.STYear, Num(SalesTargets.STMonth))) AS MinDate,
 Max(MakeDate(SalesTargets.STYear, Num(SalesTargets.STMonth))) AS MaxDate
RESIDENT SalesTargets;
Let vMinTrDate = Peek('MinDate');
Let vMaxTrDate = Peek('MaxDate');
DROP TABLE tempDates;

... and then use the date island load.

Jonathan

Set Analysis - field1 to follow field2 values?

Mon, 27 Sep 2010 09:06:05 GMT

Hi Aldo,

I am working in same type of scenario (to make date-calender-uniform)

u can apply changes to your script according to this script .....

Treat this script as your refrence ..........

LOAD

* INLINE

[

;

BranchZone:

LOAD

BRANCHID

BRANCHDESC

as BRANCH

BRANCHDESC

as DSTBranch

BRANCHDESC

as CONN_BRANCH

BRANCHDESC

as DSABranch

BRANCHDESC

as NINTBranch

ZONE

CAT

RPC

FROM

\..\..\QVD\BRANCH_ZONE_CAT.qvd

(

qvd)

;

LoginMaster:

LOAD

APPLID

FILENO

AS FNO

BRANCH

LAA_PRODUCT_ID_C

as PRODUCT_TYPE

AMOUNT

date

(LOGGED_IN_DATE,'DD/MM/YYYY') as Date

year

(LOGGED_IN_DATE) as Year

month

(LOGGED_IN_DATE) as month

week

(LOGGED_IN_DATE) as week

day

(LOGGED_IN_DATE) as day

YearName

(LOGGED_IN_DATE,0,4) as YearName

year

(date(LOGGED_IN_DATE,'DD/MM/YYYY')) & '-' & month(date(LOGGED_IN_DATE,'DD/MM/YYYY')) as YearMonth

year

(LOGGED_IN_DATE) & num(month(LOGGED_IN_DATE),'00') as YearMonthVal

year

(LOGGED_IN_DATE) & num(month(LOGGED_IN_DATE),'00') & num(day(LOGGED_IN_DATE),'00') as YearMonthDay

monthname

(LOGGED_IN_DATE) as YM,

//new field for variable

//Year(LOGGED_IN_DATE)&DATE(LOGGED_IN_DATE,'MM') AS YM,

BRANCH & '-' & monthname(LOGGED_IN_DATE) as %YMBranch

SALESOFFICE_NAME

'Login'

Source

FROM

\..\..\QVD\Logins.qvd

(

qvd)

;

SanctionMaster:

LOAD

AGREEMENTID as APPLID

FILENO

//AGREEMENTNO,

// CUSTOMERNAME,

BRANCH

PRODUCT

as PRODUCT_TYPE

// TENURE,

// ROI,

SANCTION_AMOUNT

date

(SANCTION_DATE,'DD/MM/YYYY') as Date

year

(SANCTION_DATE) as Year

month

(SANCTION_DATE) as month

week

(SANCTION_DATE) as week

day

(SANCTION_DATE) as day

YearName

(SANCTION_DATE,0,4) as YearName

year

(date(SANCTION_DATE,'DD/MM/YYYY')) & '-' & month(date(SANCTION_DATE,'DD/MM/YYYY')) as YearMonth

year

(SANCTION_DATE) & num(month(SANCTION_DATE),'00') as YearMonthVal

year

(SANCTION_DATE) & num(month(SANCTION_DATE),'00') & num(day(SANCTION_DATE),'00') as YearMonthDay

monthname

(SANCTION_DATE) as YM,

//new field for variable

// Year(SANCTION_DATE)&DATE(SANCTION_DATE,'MM') AS YM,

BRANCH & '-' & monthname(SANCTION_DATE) as %YMBranch

//FLAG,

SALESOFFICE_NAME

'Sanction'

Source

FROM

\..\..\QVD\SALES_OFFICE_WISE_SANCTION.qvd

(

qvd)

.........I am sure this will help you......

Regards,

Dushyant

;

Set Analysis - field1 to follow field2 values?

Mon, 27 Sep 2010 10:45:02 GMT

Hi there,

The changes in data model, (TempDates and DateIsland) seems to be okay, but when using TrMonth in the expression nothing happens:

TempDates:

LOAD

Min(MakeDate(SalesTargets.STYear, Num#(SalesTargets.STMonth))) AS MinDate,

Max(MakeDate(SalesTargets.STYear, Num#(SalesTargets.STMonth))) AS MaxDate

FROM [$(FinalTablesPath)SalesTargets-$(prioritySuffix).qvd] (qvd);

Let vMinTrDate = Peek('MinDate');

Let vMaxTrDate = Peek('MaxDate');

DateIsland:

LOAD

TrDate,

Month(TrDate) AS TrMonth,

Mid(TrDate,4,2) AS TrMonthNo,

Year(TrDate) AS TrYear;

LOAD

Date('$(vMinTrDate)' + RecNo() - 1) AS TrDate

AUTOGENERATE '$(vMaxTrDate)' - '$(vMinTrDate)' + 1;

DROP TABLE TempDates;

//--------------

The expression in the table is: =if(IvMonthNo = TrMonthNo, sum(QtyByBaseProduct))

In addittion, when selecting a value in the listbox "TrMonthNo", nothing happens (no selection) in listbox "IvMonthNo"

What's missing?

Set Analysis - field1 to follow field2 values?

jonathandienst — Mon, 27 Sep 2010 11:30:20 GMT

Aldo

Not sure what the problem is. I would check that the DateIsland table has the number of records you would expect (check via the Table Viewer), and I would also check the values of vMinTrDate and vMaxTrDate (put =Date(vMinTrDate) into a text box).

BTW and off-topic - you don't really need to create field TrMonthNo, as TrMonth is a dual value, having a text form of the month short name, and a numerical value equal to the month number.

Jonathan

Set Analysis - field1 to follow field2 values?

jonathandienst — Mon, 27 Sep 2010 11:36:33 GMT

Dushyant

It is very hard to make sense of your post with the script spread out like that.

When posting code I suggest that you copy the script to a text editor (notepad is fine) and then into your post, and wrap the code in a code tags. See http://community.qlik.com/forums/t/15789.aspx for more info.

Jonathan

Set Analysis - field1 to follow field2 values?

Mon, 27 Sep 2010 11:42:05 GMT

Jonathan,

I checked the variables and everything seems to be okay. I am still trying to make it work...

Thanks for your help,

Aldo.

Set Analysis - field1 to follow field2 values?

Anonymous — Mon, 27 Sep 2010 14:32:19 GMT

Aldo,

Looks like it's getting much more complex when it should be. If you upload your application, I think it would take a few minutes to help. Except I will not have free time in the next 10 hours or so...
If nobody replies by then, I'll upload a simple example.

Set Analysis - field1 to follow field2 values?

Anonymous — Tue, 28 Sep 2010 00:12:11 GMT

Hi Aldo,

See attached very simple example. I hope it will help to resolve your problem.

Set Analysis - field1 to follow field2 values?

Thu, 30 Sep 2010 21:10:07 GMT

Thanks man,

I will try it and let you know.

Aldo.