Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create reprot which should give report as for group dimension, based on days buckting i need to get count of values.When i am using below expression in column dimension, i am getting error as invalid dimension.can any one suggest on this
=if( max({$} total
Date)-only({$} Date)<3,
'1-3 days',
if( max({$} total
Date)-only({$} Date)<6,
'4-6 days',
if( max({$} total
Date)-only({$} Date)<10,
'greater than 6 days','greater than 11 days'
)
)
)
by adding the expression under column dimension, we are not getting any error for expression now, but when i select a date at filter pane, i am getting only that date below in report where my requirement is to bring previous 10 days from selected date and bucketing of days should happen.Attached the report result with aggr expression
I thought you have attached some sample data, But, you have exported the data. Could you help us to see your data and what you done in QVW? If so, we really don't need to spend time to get understand. Perhaps add NODISTINCT Keyword
Aggr(NODISTINCT if( max({$<DATA_DATE={"=$(=max(DATA_DATE))"}>} total DATA_DATE)-only({$<DATA_DATE={"<=$(=max(DATA_DATE))"}>} DATA_DATE)<30,
'1-30 days',
if( max({$<DATA_DATE={"=$(=max(DATA_DATE))"}>} total DATA_DATE)-only({$<DATA_DATE={"<=$(=max(DATA_DATE))"}>} DATA_DATE)<60,
'31-60 days',
if( max({$<DATA_DATE={"=$(=max(DATA_DATE))"}>} total DATA_DATE)-only({$<DATA_DATE={"<=$(=max(DATA_DATE))"}>} DATA_DATE)<90,
'61-90 days',
'greater than 90'))
), DATA_DATE))
By adding nodistinct also not helpfull.
Attached qvf with sample daata
Let me explain my requirement here one more time, i need to select date at filter,
when i select date,
report should get 90 days less than equal the select date and that days should be bucketed in 1-30 days,31-60 days,61-90 days' ,
and we should see this date bucketing column at column dimension , 1-30 days,31-60 days,61-90 days' ,place.
I tried my level best in explaining this i hope.
Does it mean that, As follows the sample data. In that, You want me to generate the Date between 14-Jan to 26-Dec? And then calculate the data based on your data?
Or, If you want to use only this data what is the expected one based on bucket?
Yes, based on the sample data, when i select a date from that date from filter i need to go back 9 days and that dates should fall under buckets.This calculated column i should be able to use as dimension.Can you please help on this.
I also tried with class function.But when i select a date at filer, the below function is bringing all days from selected date -30 to max number of dates in that column. Can you please suggest how i can get only 30 previous days from the selected date.
replace(class(num(Date((DATA_DATE1),'DD-MM-YYYY'))-DATA_DATE1,30,'x',30),'<= x =<','-')