Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

manigunta
Contributor

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
Esteemed Contributor

Re: Compare data with last 6 and 18th months

Hi,

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

PFA

manigunta
Contributor

Re: Compare data with last 6 and 18th months

HI,


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

OmarBenSalem
Esteemed Contributor

Re: Compare data with last 6 and 18th months

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')

manigunta
Contributor

Re: Compare data with last 6 and 18th months

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)

manigunta
Contributor

Re: Compare data with last 6 and 18th months

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
Esteemed Contributor

Re: Compare data with last 6 and 18th months

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?

manigunta
Contributor

Re: Compare data with last 6 and 18th months

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')

manigunta
Contributor

Re: Compare data with last 6 and 18th months

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')

Community Browser