Complex (impossible?) expression

[Utop1984]

Hello Everyone,

I have a problem with an expression today . I think what Iwant to achieve is impossible but.. But maybe someone will have a magical idea 😉 ?

We sell a product  with a base on wich can be added options.

Information are stored in a database in 2 differents tables :

1 stored the SN (serial number of a system - unique indentifier of a system sold) with global options selected by the customer , Attention the customer can begin with Base system with or not options and in the future he can add Options, it will be still 1 line but add info to the SN : simplified exemple :

BASE 1 :

SN	Base	OPTION 1	OPTION 2	OPTION 3
1111	1	1	0	1
2222	1	1	1	1
3333	1	1		1
4444	1	0	0	1
5555	1	0	0	1
6666	1	0	0	1

If I want the total of Option 3 in the market : answer is 6

Now I want have the number of this option 3 (or even system it is the same problem), sold per year or month, 

I have an other database , which store every Orders by SN and date.

So every time I have an entry it is a new line, If a customer update his system or add a new options :

SN	Order	Date
1111	1	janv-05
2222	2	mars-06
3333	3	juin-07
4444	4	janv-07
5555	5	mai-07
6666	6	mars-08
1111	7	juin-08
3333	8	juin-09
2222	9	juil-10
2222	10	mars-11
1111	11	juil-12

Problem :

I joined the 2 tables with Qlik to chart my sales per year or month.

But If I make a chart to display the number of Options 3 or Systems per year for example (or month), The total through the years will be 11 instead of 6 because it will show every orders of Systems with the Option 3 for example. (If I want numbers of Option 1 instead by years, the real total is 3 but if I am charting it by years, it will display a total of 8 (because it will show every orders containing SN with option 1).

The only solution I came up with was to do something like :

To have Options 3 sold in 2012 for example

(Total of Options 3 from year 0 to year 2012) - (Total of Options 3 from 0 to 2011)

But this will give me number but I can't chart it anymore by using standard chart with Date as a dimension... Right ?

I know it is not easy to understand but if somebody has an idea :)... 

[Vegar]

What if you don't join, but keep the tables as two connected with the SN dimension?

[treysmithdev]

Is this what you are looking for?

Count(distinct {<[OPTION 3]={1}>} SN)

[Utop1984]

@treysmithdev:Thanks a lot for your help but unfortunately it is not exctly what am I looking for (even if it helps to be honest :))

In the exemple as you show the number of OPTION 3 sold betsween 2005 and 2012 is 6.

My target is to chart the sales of this option per year to analyze the trend among the year.

In your happens excatly what I have today : The real TOTAL is 6 but It ffels like when we look the yearly chart that we sold 11 Option 3. 

This how I would like the chart to look like :

Your solution is helping as it enables to display the actual total between year X and Y but I can't chart it ...

I understand that this is because of how the database was set and that a perfect solution might not exist...

Important even if it is un true - Like I show in the exemple above - I am ready to accept a solution where the Option 3 will be displayed in the year where SN was first ordered even if we don't know that the option 3 was sold with the first order or not... There is no way in the database to know this information so I prefer this uncertainty compared to the other...

If we find a solution for that and to display the chart like above I will be more than happy 🙂

Again : Thanks  a lot for your valuable help !!!

[Utop1984]

@Vegar: Thanks for your comment but I don't see how it would solve the problem (but I am a total beginner).

Also I think I was wrong I did a join in the beginning but afterwards I changed to do only an association with the Idea and I still have the problem :

But thanks for your help and again, there might be something I didn't understand so don't hesitate to correct me 🙂

[JGMDataAnalysis]

Script:

SET HidePrefix = '_';
SET NullInterpret = '-';

T1:
NOCONCATENATE
LOAD * INLINE [
    SN,	  Base,	OPTION 1, OPTION 2, OPTION 3
    1111, 1,	1,		  0,		1
    2222, 1,	1,		  1,		1
    3333, 1,	1,	 	  -,		1
    4444, 1,	0,		  0,		1
    5555, 1,	0,		  0,		1
    6666, 1,	0,		  0,		1
];

// The table MUST be sorted in ascending mode 
// by the date of the order to define the _Flag field.
// In this example the order is defined in the INLINE.

T2:
NOCONCATENATE
LOAD SN, SN AS tmpSN, Order,
    Date(Date#(Date, 'MMM-YY'), 'MMM-YY') AS MonthYear,
    Year(Date#(Date, 'MMM-YY')) AS Year,
    If(Exists(tmpSN, SN), 0, 1) AS _Flag
INLINE [
	SN,	  Order, Date
    1111, 1,	 jan-05
    2222, 2,	 mar-06
    3333, 3,	 jun-07
    4444, 4,	 jan-07
    5555, 5,	 may-07
    6666, 6,	 mar-08
    1111, 7,	 jun-08
    3333, 8,	 jun-09
    2222, 9,	 jul-10
    2222, 10,    mar-11
    1111, 11,	 jul-12
];

DROP FIELD tmpSN;

Measure:

Count({<[_Flag] = {1}, [OPTION 3] = {1}, [MonthYear]=>} Distinct [SN])

Result:

[Utop1984]

Spoiler

@JGMDataAnalysis:

Thanks a lot, I tried your script  with Excel sheets : it works and result are like what I am expecting !

Nevertheless I simplified the example compared to my real database, I see 2 majors differences that make it difficult to apply on my real datas for the moment :

1/ In the exemple you use the Inline command to  sort the data in the right Order, but in my real life it is a database : so in can't use Inline command : I have thousands of SN and new SN are added to the database every day... so Inline command can't be use at least not like that...

2/ I simplified the example : the real date format is :

18/06/2014 00:00:00

I guess I should change the script by :

LOAD SN, SN AS tmpSN, Order,
    Date(Date#(Date, 'DD-MM-YYYY'), 'MM-YYYY') AS MonthYear,
    Year(Date#(Date, 'MM-YYYY')) AS Year,
    If(Exists(tmpSN, SN), 0, 1) AS _Flag

Right ? (I now it sounds easy but I am not a developper :(...)

But Thanks a lot for your help ! this problem seemed impossible to solve for me and I feel solution is really close !! 🙂

[JGMDataAnalysis · Accepted solution]

"1/ In the exemple you use the Inline command to  sort the data in the right Order, but in my real life it is a database : so in can't use Inline command : I have thousands of SN and new SN are added to the database every day... so Inline command can't be use at least not like that..."

I almost always use an Inline as input table to answer the issues that are published in this community for simplicity. The idea is to provide you with a conceptual solution. In your case, you should generate the connection to the DB and replace the Load Inline with a Preceding SQL query ordered by the corresponding date in ascending mode.

2/ I simplified the example : the real date format is :

18/06/2014 00:00:00

I guess I should change the script by :

LOAD SN, SN AS tmpSN, Order,
Date(Date#(Date, 'DD-MM-YYYY'), 'MM-YYYY') AS MonthYear,
Year(Date#(Date, 'MM-YYYY')) AS Year,
If(Exists(tmpSN, SN), 0, 1) AS _Flag

Something like this...

Date(Floor(Date#(Date, 'DD/MM/YYYY hh:mm:ss')), 'MM-YYYY') AS MonthYear,

Beyond the interpretation and format of dates, it would be convenient to generate a master calendar that communicates with your table through the [Date] field. Under this circumstance, the [MonthYear] and [Year] fields (among other fields such as Weekday, Week, Quarter and else) would integrate the MasterCalendar table.

I suggest you that you review the content of the following link:

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/