Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I wanted to create a filter pane for my bar chart data
the filter pane should have like last 5 year ,last 10 year ,last 15 year like this option
but the problem is that i have data in year format like 2001 ,2002 etc
how to create filter pane so user can select directly last 5 year last 10 etc data
please help
Hi
Try like below
LET vDMNoOfYear =20;
Let vMaxDate = Floor(YearEnd(Today()-1));
LET vMinDate = Floor(YearStart(AddYears(Today(), -($(vDMNoOfYear)))));
MasterCalendar:
LOAD
Year(CalDate) as Year,
WeekDay(CalDate) as WeekDay,
'Q' & Ceil(Month(CalDate)/3) as Quarter,
QuarterName(CalDate) as QuarterName,
Month(CalDate) as Month,
If(Month(CalDate) = Month(QuarterEnd(CalDate)), 1, 0) as MaxMonthFlag,
Date(MonthStart(CalDate), 'MM-YYYY') as MonthYear,
1000*Rand() as Sales;
LOAD
Date($(vMinDate) + IterNo()-1) as CalDate
AutoGenerate 1
While $(vMinDate) + IterNo()-1 <= $(vMaxDate);
LOAD QuarterName, Max(MonthYear) as MaxMonthYearPerQuarter Resident MasterCalendar Group by QuarterName;
For i=5 to 20 step 5
Load Distinct Year, 'Last '& $(i) & ' Years' as LastNYearFlag Resident MasterCalendar
Where Year > Year(Today())-$(i);
next
o/p:
Hi
Try like below
LET vDMNoOfYear =20;
Let vMaxDate = Floor(YearEnd(Today()-1));
LET vMinDate = Floor(YearStart(AddYears(Today(), -($(vDMNoOfYear)))));
MasterCalendar:
LOAD
Year(CalDate) as Year,
WeekDay(CalDate) as WeekDay,
'Q' & Ceil(Month(CalDate)/3) as Quarter,
QuarterName(CalDate) as QuarterName,
Month(CalDate) as Month,
If(Month(CalDate) = Month(QuarterEnd(CalDate)), 1, 0) as MaxMonthFlag,
Date(MonthStart(CalDate), 'MM-YYYY') as MonthYear,
1000*Rand() as Sales;
LOAD
Date($(vMinDate) + IterNo()-1) as CalDate
AutoGenerate 1
While $(vMinDate) + IterNo()-1 <= $(vMaxDate);
LOAD QuarterName, Max(MonthYear) as MaxMonthYearPerQuarter Resident MasterCalendar Group by QuarterName;
For i=5 to 20 step 5
Load Distinct Year, 'Last '& $(i) & ' Years' as LastNYearFlag Resident MasterCalendar
Where Year > Year(Today())-$(i);
next
o/p:
hi can you explain this 1000*Rand() as Sales; I am not creating for sales i don't
understand it
Hi
Its provides random values between 0 to 1 and multiply by 1000.. for testing