topic Re: Select data for previous month, which is not in current month in QlikView

Select data for previous month, which is not in current month

Mon, 02 Mar 2015 08:25:15 GMT

Hey everybody!

Could you please help me with sth? I have data for jan 2015 and for feb 2015. Some of the data is just in jan 2015, some is just in feb 2015 and some of the data is in both months. How can I display data in a table that is just in jan 2015 and not in feb 2015? I need this for both directions. I need sth like this: display data if in previous month and not in current month.

Thanks and regards,

Anja

Re: Select data for previous month, which is not in current month

kamalqlik — Mon, 02 Mar 2015 09:51:09 GMT

Hi Anja,

As I understand your point...You want to say that Jan data will be fixed and Current Month Data will keep on changing.

For Example in Feb Baseline Data will be Jan

and Current Data will be Feb

In Case of Mar BaseLine Data will be Jan and Current Data will be Mar....

Use Like this

SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Depart:
LOAD * Inline
[
Department, Value, Date

it , 10 , 31/01/2015

it , 20 , 31/01/2015

it , 30 , 31/01/2015

it , 40, 31/01/2015

it , 10 , 28/02/2015

it , 20 , 28/02/2015

it , 30 , 28/02/2015

it , 30, 28/02/2015 ];

Depart1:
LOAD *,
Month(Date) as Month
Resident Depart;
DROP Table Depart;

//After this reload the Application.

In Front-end make an Variable vMAxDate=Month(Max(Date))

So Baseline Data will be----

=sum({<Month={'Jan'}>}Value)

and Current Data will be-

=sum({<Month={'$(MAxDate)'}>}Value) .

I think this will help you ...

Regards,

Kamal

Re: Select data for previous month, which is not in current month

hariprasadqv — Mon, 02 Mar 2015 09:59:21 GMT

Hi Anja,

My view may give you a thought of resolving your concern some how,

i.e

use Count for that user in the years 2015 then if the count is 2 or month is February then exclude the user. It is better to go for set analysis if you concern about the UI level.

Re: Select data for previous month, which is not in current month

Mon, 02 Mar 2015 10:49:42 GMT

I was thinking about this option, but how can i exclude this data?

I have table (but whit 200000 rows of data) sth like this:

For numbers 340349 and 340620 are two records (2015/01 and 2015/02). For numbers 310224 and 340570 is one record (2015/01). I want to display just numbers that have just one record (2015/01). How can i achieve that?

Re: Select data for previous month, which is not in current month

simenkg — Mon, 02 Mar 2015 10:57:24 GMT

You can use the Set operator "/" which is XOR or Either or but not both. :

Arguments:

Operator	Description
+	Union. This binary operation returns a set consisting of the records that belong to any of the two set operands.
-	Exclusion. This binary operation returns a set of the records that belong to the first but not the other of the two set operands. Also, when used as a unary operator, it returns the complement set.
*	Intersection. This binary operation returns a set consisting of the records that belong to both of the two set operands.
/	Symmetric difference (XOR). This binary operation returns a set consisting of the records that belong to either, but not both of the two set operands

Sum({<[Serial No_]={'2015/01'}>/<[Serial No_]={'2015/02'}>} Amount)

Re: Select data for previous month, which is not in current month

Mon, 02 Mar 2015 11:14:44 GMT

Hi Simen,

This is not working in my case If i use statement like yours (I tried / and -) in my expression, i don't get correct results. It displays also number 340349, which i don't want to have in my table. Just those with one record.