Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How can we fix Hardcoding in Visualisation??

 

if(GetSelectedCount([Month])=0 and GetSelectedCount([Fiscal Year])=0,

 

 

num(sum({<calendar_current_month_offset={'$(vDefaultMonthABCInventory)'}, month_end_metric_date_key={'20161031'}>}forecast),'#,##0'), The set analysis should be when the month_end_date_key = month_end_metric_date_key

 

 

num(sum({<calendar_year_month =  {'$(=Max(calendar_year_month))'}>}forecast),'#,##0')

 

)

   

23 Replies
rupamjyotidas
Specialist
Specialist

in a text box type '=$(varMaxDate)'

Probably better if you share what you written in script for varMaxDate

Not applicable
Author

In Script

Temp:
Load
               Min(month_end_date_key) as minDate,
               Max(month_end_date_key) as maxDate
Resident month_end_date_key;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));


D_MONTH_END_DATE_TEMP:
NoConcatenate

LOAD *,
dual(Month_Name, ApplyMap('Month_Sort',Month_Name)) as Month;
//dual(Month_Name, ApplyMap('Month_Sort',Month_Name)) as [Month], fiscal_year &'-'& [Month_Name] as [Year Month];

LOAD
    month_end_date_key,
    month_end_day_date,
    makedate(calendar_year,calendar_month_number_in_yr,left(month_end_day_date,2)) as month_end_day_date_converted,
    //Date(Date#(month_end_day_date,'DD-MMM-YY'),'M/DD/YYYY') as month_end_day_date_converted,
    month_end_day_name,
    day_number_in_calendar_year,
    day_number_in_fiscal_year,
    //Month(subfield(calendar_month_name,3,0)) as MONTH,
    //dual(calendar_month_name, ApplyMap('Month_Sort',calendar_month_name)) as Month,
    calendar_month_name,
    left(calendar_month_name,3) as Month_Name,
    calendar_month_number_in_yr,
    calendar_year_month,
    //Max(calendar_year_month) as MaxMonth,
    calendar_qtr,
    calendar_qtr_name,
    calendar_year_qtr,
    calendar_year,
    fiscal_month_number_in_yr as [FY Period],
    fiscal_year_month,
    //FISCAL_YEAR&'-'&MonthName as [Year Month],
    fiscal_qtr as [Fiscal Qtr],
    fiscal_qtr_name,
    fiscal_year_qtr,
    fiscal_year as [Fiscal Year],
    //[fiscal_year] &'-'& [calendar_month_name] as [Year Month],
    month_start_date_key,
    month_start_day_date,// as [Date]
    makedate(calendar_year,calendar_month_number_in_yr,left(month_start_day_date,2)) as month_start_day_date_converted,
    date(today()-1) as YesterdaysDate,
    If(makedate(calendar_year,calendar_month_number_in_yr,left(month_end_day_date,2)) >= (date(today()-1)),'TRUE','FALSE') as GreaterThanYesterday,
    If(makedate(calendar_year,calendar_month_number_in_yr,left(month_start_day_date,2)) <= (date(today()-1)),'TRUE','FALSE') as LessThanYesterday
FROM
(txt, utf8, embedded labels, delimiter is '|', no eof)
where len(month_end_date_key)=8;

Let vTestValues = 'Not True';

D_MONTH_END_DATE:
NoConcatenate
Load *,
//if(date(month_end_day_date_converted)=Max(date(month_end_day_date_converted)),1,0) as calendar_current_month,
//if(date(month_end_day_date_converted)>=(today()-10) and date(month_start_day_date_converted)<=(today()-10),1,0) as calendar_current_month,
if(date(month_end_day_date_converted)>=(today()-1) and date(month_start_day_date_converted)<=(today()-1),1,0) as calendar_current_month,
//if(month_end_day_date_converted>=YesterdaysDate and month_start_day_date_converted<=YesterdaysDate,1,0) as calendar_current_month,
//if(calendar_year_month='201606',1,1) as calendar_current_month,
RowNo() as calendar_year_month_rank
Resident D_MONTH_END_DATE_TEMP
order by month_end_date_key desc;

Drop table D_MONTH_END_DATE_TEMP;
//exit script;

//Max:
//LOAD month_end_date_key, Max(month_end_day_date_converted) as MaxMonth, Resident D_MONTH_END_DATE Group By month_end_date_key;


D_MONTH_END_DATE_CURRENT_MONTH:
NoConcatenate
Load
calendar_year_month_rank
Resident
D_MONTH_END_DATE;
//where
//calendar_current_month='1';

Let vCurrentMonth=peek('calendar_year_month_rank',1,'D_MONTH_END_DATE_CURRENT_MONTH');

Drop table D_MONTH_END_DATE_CURRENT_MONTH;

//exit script;

left join(D_MONTH_END_DATE)

Load
month_end_date_key,
calendar_year_month_rank-$(vCurrentMonth) as calendar_current_month_offset

Resident
D_MONTH_END_DATE;

Store D_MONTH_END_DATE into (qvd);

and in KPI

if(GetSelectedCount([Month])=0 and GetSelectedCount([Fiscal Year])=0,

//Currently, defaulted to last month until current month data is validated and tested (current month would be offset of -1
    num(sum({<calendar_current_month_offset={'$(vDefaultMonthABCInventory)'}, month_end_metric_date_key={'$(varMaxDate)'}>}forecast),'#,##0'),
//calendar_year_month =  {'$(=Max(calendar_year_month))'}
num(sum({<calendar_year_month =  {'$(=Max(calendar_year_month))'}>}forecast),'#,##0')
   

Not applicable
Author

Also I get the following error when I try to debug it.

rupamjyotidas
Specialist
Specialist

Did you just copy and paste what I wrote. :-). It was an example: probably this would give more clarity

PUT this script after you calendar table

Temp: 

Load 

               Min(month_end_date_key) as minDate, 

               Max(month_end_date_key) as maxDate 

Resident 'Your calendar table where you have field month_end_date_key'

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

Not applicable
Author

After doing this I got value in KPI as 4M but the correct value is 6.25M

rupamjyotidas
Specialist
Specialist

Did you check the value of  $(varMaxDate).

Probably you need to check the variables in your equation

Not applicable
Author

I checked the date it comes as 20170131. I need the date of 20161031

rupamjyotidas
Specialist
Specialist

I thought you were looking for the max date. You need to modify the peek or

the field to get the result

Not applicable
Author

What exactly should be the modification for peek function?

rupamjyotidas
Specialist
Specialist

I don't know if you are looking for the max dates or dates in between, best would be use Order by Dates below

Load 

               Min(month_end_date_key) as minDate, 

               Max(month_end_date_key) as maxDate 

Resident 'Your calendar table where you have field month_end_date_key' order by month_end_date_key asc

and also read about peek below

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/InterRecordFunctions/P...