Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
I suppose that you want to compare last 6 months Value with value between last 18 months and last 12 months.
PFA
HI,
We need to take 24 months data and in that last 6 months value should compare with first 18 months data
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')
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)
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
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?
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')
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')