Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Compare data with last 6 and 18th months

i have data with two columns as the date and data1 columns as below

date                data1

01/01/2017     1

I have to compare data1 in last 6 months and data1 in last 18 months. if sum of data1 in last 6 months is greater than last 18 months then i have to display as "well" in the qliksense report.

For 18 months: we have to get first 18th months in last 24 months

can anyone suggest

8 Replies
YoussefBelloum
Champion
Champion

Hi,

I suppose that you want to compare last 6 months Value with value between last 18 months and last 12 months.

PFA

Anonymous
Not applicable
Author

HI,


We need to take 24 months data and in that last 6 months value should compare with first 18 months data

OmarBenSalem

for first 18 of last 24 months:

sum({<Year,Month,Date={">=$(=addmonths(max(Date),-24)) <$(=addmonths(max(Date),-6))"}>}Measure)

for first 6 months:

sum({<Year,Month,Date={">=$(=addmonths(max(Date),-6))"}>}Measure)

so, u can build a table like follow

dimension: Your dimension

measure:

if(sum({<Year,Month,Date={">=$(=addmonths(max(Date),-6))"}>}Measure)>=

sum({<Year,Month,Date={">=$(=addmonths(max(Date),-24)) <$(=addmonths(max(Date),-6))"}>}Measure),

'Well','Not Well')

Anonymous
Not applicable
Author

HI

thanks for reply

but i am getting some issue. i used above query to get last 6 months data. seems like it is not working.

tried below.

=addmonths(max(Timestamp(Timestamp#(datecolumn, 'YYYYMMDD') ,'DD/MM/YYYY')),-6)

Anonymous
Not applicable
Author

Even tried this, where it always brings 'Well'

if(sum({<Year,Month,Date={">=$(=addmonths(max(Date),-6))"}>}Measure)>=

sum({<Year,Month,Date={">=$(=addmonths(max(Date),-24)) <$(=addmonths(max(Date),-6))"}>}Measure),

'Well','Not Well')


when i  cross checked data for "addmonths(max(Date),-6)"  , this is not bringing last 6 months dates

OmarBenSalem

It all depends on your Date, how it's formatted, is qlik recognizing it as a date etc..

Can u share a sample app maybe?

Anonymous
Not applicable
Author

alos getting error while creating this dimenion at {

if(sum({<Year,Month,Date={">=$(=addmonths(max(Date),-6))"}>}Measure)>=

sum({<Year,Month,Date={">=$(=addmonths(max(Date),-24)) <$(=addmonths(max(Date),-6))"}>}Measure),

'Well','Not Well')


can you please help me

i tried as this

if(sum({<Year,Month,Date={">=$(=addmonths(max(Timestamp(Timestamp#(datecolumn, 'YYYYMMDD') ,'DD/MM/YYYY')),-6))"}>}Measure)>=

sum({<Year,Month,Date={">=$(=addmonths(max(Timestamp(Timestamp#(datecolumn, 'YYYYMMDD') ,'DD/MM/YYYY')),-24)) <$(=addmonths(max(Timestamp(Timestamp#(datecolumn, 'YYYYMMDD') ,'DD/MM/YYYY')),-6))"}>}Measure),

'Well','Not Well')

Anonymous
Not applicable
Author

alos getting error while creating this dimenion at {

if(sum({<Year,Month,Date={">=$(=addmonths(max(Date),-6))"}>}Measure)>=

sum({<Year,Month,Date={">=$(=addmonths(max(Date),-24)) <$(=addmonths(max(Date),-6))"}>}Measure),

'Well','Not Well')


can you please help me

i tried as this

if(sum({<Year,Month,Date={">=$(=addmonths(max(Timestamp(Timestamp#(datecolumn, 'YYYYMMDD') ,'DD/MM/YYYY')),-6))"}>}Measure)>=

sum({<Year,Month,Date={">=$(=addmonths(max(Timestamp(Timestamp#(datecolumn, 'YYYYMMDD') ,'DD/MM/YYYY')),-24)) <$(=addmonths(max(Timestamp(Timestamp#(datecolumn, 'YYYYMMDD') ,'DD/MM/YYYY')),-6))"}>}Measure),

'Well','Not Well')