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

[#1]

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

[kamalqlik]

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

[hariprasadqv]

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.

[#4]

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?

[simenkg]

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)

[#6]

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.