Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Complex (impossible?) expression

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 :

SNBaseOPTION 1OPTION 2OPTION 3
11111101
22221111
333311 1
44441001
55551001
6666100

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 :

SNOrder Date
11111janv-05
22222mars-06
33333juin-07
44444janv-07
55555mai-07
66666mars-08
11117juin-08
33338juin-09
22229juil-10
222210mars-11
111111juil-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 :)... 

 

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

Re: Complex (impossible?) expression

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

View solution in original post

7 Replies
Highlighted
Partner
Partner

Re: Complex (impossible?) expression

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Luminary
Luminary

Re: Complex (impossible?) expression

Is this what you are looking for?

 

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

 

 
 

 

complex-expression.jpg

Blog: WhereClause   Twitter: @treysmithdev
Highlighted
Contributor III
Contributor III

Re: Complex (impossible?) expression

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

Capture.PNG

 
 
 
 

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

 

 

Highlighted
Contributor III
Contributor III

Re: Complex (impossible?) expression

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

Capture2.PNG

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

 

Highlighted
Creator II
Creator II

Re: Complex (impossible?) expression

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:

QlikCommunityChallenge_SN_O3.PNG

Highlighted
Contributor III
Contributor III

Re: Complex (impossible?) expression

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




Highlighted
Creator II
Creator II

Re: Complex (impossible?) expression

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

View solution in original post