Not Getting Values in variable

[Hania]

Hello Team,

Please Help me to reach out at solution.

I Have created a one Text file in that  one variable which have multiple if condition for MTD , QTD, HTD, YTD.

but when ever I am going to run variable in Qliksense  after uploading a file its giving me  expression for  MTD , QTD, HTD, YTD not value .

Below mentioned  code that I am Using in Text file.

SET vSelect_MTD_QTD_HYTD_YTD='MTD';

Set vDateRange=if((vSelect_MTD_QTD_HYTD_YTD)='MTD',
'(num(Sum({<FiscalMonthName=,FiscalQuarter=,FiscalYear= ,Date={">='&chr(36)&'(=MonthStart('&chr(36)&'(vMax_CY)))<='&chr(36)&'(=Date('&chr(36)&'(vMax_CY)))"} >}[Invoice Domestic Rate])/'&chr(36)&'(vLacs),'&chr(36)&'(vRoundOff)))',

if((vSelect_MTD_QTD_HYTD_YTD)='QTD',
'(num(Sum({<FiscalYear=,FiscalMonthName=,FiscalQuarter=,Date=,FiscalYearNum={"'&chr(36)&'(=max(FiscalYearNum))"},
FiscalQuarterNum={"'&chr(36)&'(=max({<FiscalYearNum={"'&chr(36)&'(=max(FiscalYearNum))"}>}FiscalQuarterNum))"}>}[Invoice Domestic Rate])/'&chr(36)&'(vLacs),'&chr(36)&'(vRoundOff)))',

if((vSelect_MTD_QTD_HYTD_YTD)='HTD',
'(num(Sum({<FiscalYear=,FiscalMonthName=,FiscalQuarter=,Date=,FiscalYearNum={"'&chr(36)&'(=max(FiscalYearNum))"},
FiscalHYNum={"'&chr(36)&'(=max({<FiscalYearNum={"'&chr(36)&'(=max(FiscalYearNum))"}>}FiscalHYNum))"}>}
[Invoice Domestic Rate])/'&chr(36)&'(vLacs),'&chr(36)&'(vRoundOff)))',

if((vSelect_MTD_QTD_HYTD_YTD)='YTD',
'Num(sum({<FiscalMonthName=,FiscalMonth=,FiscalQuarter=,FiscalYear=,Date={">='&chr(36)&'(='&chr(36)&'(vMin_CY))<='&chr(36)&'(='&chr(36)&'(vMax_CY))"}>}[Invoice Domestic Rate])/'&chr(36)&'(vLacs),'&chr(36)&'(vRoundOff))',

))));

[Primrose]

Hi Hania,

If I understand you correctly, vSelect_MTD_QTD_HYTD_YTD works as expected, but for vDateRange you don't get what expected when you use it on the front end. Do you use the dollar-sign expression (e.g. $(vDateRange))?

[Hania]

yes when i am calling $(vDateRange) then its work properly but giving me the expression (that i have written for calculating the MTD, YTD, QTD,HTD ) for MTD , YTD so on ,

instead of expression i want value of MTD, YTD so on. 

[tealowk]

Hi,

if you want to assign a calculated value to a variable you have to use the LET statement instead of the SET statement.

LET vDateRange = if(...);

Hope that helps.

Kind regards from Brussels,

Thilo

[marcus_sommer]

I suggest to create appropriate flag-fields within the calendar - maybe several ones whereby the #TD could be nested within a single one, for example:

if(inmonthtodate(Date, today(), 0), 1,
if(inquartertodate(Date, today(), 0), 2,
if(inyeartodate(Date, today(), 0) and Month <= 6, 3,
if(inyeartodate(Date, today(), 0), 4), 5))) as Flag

and the expression may look like:

sum({< Flag = {"<=$(vSelect)"}>} Value)

[Hania]

Thanks @tealowk , I Agree with you ,

But when I am using Let instead of Set then its giving me only MTD Value,

means in vDateRange variable there is only MTD value are storing after using Let.

(Not giving YTD,HTD,QTD value ) .

[tealowk]

Okay, I had a closer look at your code and found several issues. Please use this code instead:

Set vSelect_MTD_QTD_HYTD_YTD='MTD';

Switch vSelect_MTD_QTD_HYTD_YTD

	Case 'MTD'
		let vDateRange = '(num(Sum({<FiscalMonthName=,FiscalQuarter=,FiscalYear= ,Date={">='&chr(36)&'(=MonthStart('&chr(36)&'(vMax_CY)))<='&chr(36)&'(=Date('&chr(36)&'(vMax_CY)))"} >}[Invoice Domestic Rate])/'&chr(36)&'(vLacs),'&chr(36)&'(vRoundOff)))';
	
    Case 'QTD'
    	let vDateRange = '(num(Sum({<FiscalYear=,FiscalMonthName=,FiscalQuarter=,Date=,FiscalYearNum={"'&chr(36)&'(=max(FiscalYearNum))"},
FiscalQuarterNum={"'&chr(36)&'(=max({<FiscalYearNum={"'&chr(36)&'(=max(FiscalYearNum))"}>}FiscalQuarterNum))"}>}[Invoice Domestic Rate])/'&chr(36)&'(vLacs),'&chr(36)&'(vRoundOff)))';

    Case 'HYTD'
    	let vDateRange = '(num(Sum({<FiscalYear=,FiscalMonthName=,FiscalQuarter=,Date=,FiscalYearNum={"'&chr(36)&'(=max(FiscalYearNum))"},
FiscalHYNum={"'&chr(36)&'(=max({<FiscalYearNum={"'&chr(36)&'(=max(FiscalYearNum))"}>}FiscalHYNum))"}>}
[Invoice Domestic Rate])/'&chr(36)&'(vLacs),'&chr(36)&'(vRoundOff)))';

    Case 'YTD'
		let vDateRange = 'Num(sum({<FiscalMonthName=,FiscalMonth=,FiscalQuarter=,FiscalYear=,Date={">='&chr(36)&'(='&chr(36)&'(vMin_CY))<='&chr(36)&'(='&chr(36)&'(vMax_CY))"}>}[Invoice Domestic Rate])/'&chr(36)&'(vLacs),'&chr(36)&'(vRoundOff))';

End Switch