Set Analysis - field1 to follow field2 values? - Qlik Community

Report Inappropriate Content · ‎2010-09-26

<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>

Anonymous · ‎2010-09-26

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.

Report Inappropriate Content · ‎2010-09-26

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.

Anonymous · ‎2010-09-26

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.

Report Inappropriate Content · ‎2010-09-27

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.

jonathandienst · ‎2010-09-27

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Report Inappropriate Content · ‎2010-09-27

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.

jonathandienst · ‎2010-09-27

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Report Inappropriate Content · ‎2010-09-27

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

,

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'

as

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'

as

Source

FROM

..

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

(

qvd)

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

Regards,

Dushyant

;

Report Inappropriate Content · ‎2010-09-27

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?