topic Re: Not Getting Values in variable in App Development

Not Getting Values in variable

Hania — Tue, 11 Jul 2023 05:19:24 GMT

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))',

))));

Re: Not Getting Values in variable

Primrose — Tue, 11 Jul 2023 07:55:08 GMT

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))?

Re: Not Getting Values in variable

Hania — Tue, 11 Jul 2023 10:06:37 GMT

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.

Re: Not Getting Values in variable

tealowk — Tue, 11 Jul 2023 11:17:28 GMT

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

Re: Not Getting Values in variable

marcus_sommer — Wed, 12 Jul 2023 08:51:55 GMT

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)

Re: Not Getting Values in variable

Hania — Thu, 13 Jul 2023 07:28:09 GMT

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 ) .

Re: Not Getting Values in variable

tealowk — Thu, 13 Jul 2023 07:42:20 GMT

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