Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i am stuck here for calculating data
From last 24 months have to check data with first 6 months and
for last 6 months based on a date column in format(YYYYMMDD) to first 18 months.
Can you anyone please suggest.
but when i am using this, i am getting 18 months of data. this working same as 18 months statement you provided me
sum( {< date={">=$(=Date(AddMonths(Date#(Max(date),'YYYYMMDD'),-6),'YYYYMMDD'))<=$(=Date(Date#(Max(date),'YYYYMMDD'),'YYYYMMDD'))" }>} data)
hmm actually i over looked 18 months logic also. it is also not working prroperly
Sorry nagamani, maybe stalwar1 have time to take a look here and give you something else
OK, THANK YOU for all you support.
I got logic for 6 months but waiting on to get first 18 months in last 24 months
Wow!! this is one long thread, somebody can brief me on what is going on
HI lalwar,
We need to get to create as dimension, where we have to compare sum of last 6 months data greater than sum of first 18 months data in last 24 months and display as Well if greater than or "notwell" is it is less than.
In part of that we are trying to get sum of data for first 18 months from last 24 months, where i am facing issue. Can you please help
we have data as below
20160301,100
20160401,100
20160501,100
20160601,100
20160701,400
20160801,500
20160901,100
20161001,100
20161101,100
20161201,100
20170101,400
20170201,500
20170301,100
20170401,100
20170501,100
20170601,100
20170701,400
20170801,500
20170901,100
20171001,100
20171101,100
20171201,100
20180101,400
20180201,500
So, based on this input data that you just provided, what will be the expected output in numerical terms you expect to get from it?
i should get date and data as output
dates: should have first 18months dates from last 24 months
sum of data: related to 18 months
dates: should have last 6 months
sum of data: related to 6 months
If we get data for above i will take of others which i need. to move forward i need this
So, something like this
In order to do this, you need to first make sure that QlikView or Qlik Sense is reading your date correctly as a date field. To do this, I had to do this in the script
Table:
LOAD Date(Date#(Date, 'YYYYMMDD')) as Date,
Value;
LOAD * INLINE [
Date, Value
20160301, 100
20160401, 100
20160501, 100
20160601, 100
20160701, 400
20160801, 500
20160901, 100
20161001, 100
20161101, 100
20161201, 100
20170101, 400
20170201, 500
20170301, 100
20170401, 100
20170501, 100
20170601, 100
20170701, 400
20170801, 500
20170901, 100
20171001, 100
20171101, 100
20171201, 100
20180101, 400
20180201, 500
];
Once you do this, the expressions are fairly simple
=Sum({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(Date), -6)))"}>}Value)
=Sum({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -5)) & '<=' & Date(AddMonths(Max(Date), 0)))"}>}Value)
thankyou,i am to get values as expected
i tried to create one dimension at report as
if(Sum({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -24)) & '<=' & Date(AddMonths(Max(Date), -6)))"}>}Value)
> Sum({<Date = {"$(='>=' & Date(AddMonths(Max(Date), -5)) & '<=' & Date(AddMonths(Max(Date), 0)))"}>}Value), 'WELL','NOTWELL')
i am getting as invalid dimension, can you help me on this please