topic Re: to write expression using variable in QlikView

to write expression using variable

sakshikaul — Wed, 25 Nov 2020 16:16:04 GMT

I have a variable named daysOfComparison declared as follows:-

=date(lastDate)-compday

where compday is the value entered by user for comparing no. of days.

Lets say if user enters 5 as comparison no. of days input box then the sales for 10/5/2018 will be displayed in chart as per formula mentioned above in variable

Note last date is 10/10/2018

Following is the image for above explanation

Now I want to write an expression for the same ie when user selects comparison(no. of days )=5 then sales for 10/5/2018 should be displayed

How to write an expression?

Re: to write expression using variable

Anil_Babu_Samineni — Mon, 29 Oct 2018 05:51:17 GMT

If you want to show only one date of value, Try this?

Sum({<lastDate = {$(=VariableName)}>} Measure) // VariableName means - Your Input box variable

Sum({<lastDate = {"=$(=Date(date(lastDate) - $(compday)))"}>} Measure)

Re: to write expression using variable

sakshikaul — Mon, 29 Oct 2018 06:10:27 GMT

sum({<lastDate = {$(=DaysOfComp)}>} [inv value])

This expression is giving no result

Sum({<lastDate = {"=$(=Date(date(lastDate) - $(compday)))"}>} [inv value])

This expression is giving result but when I am entering value as comparison no. of days as 5 the corresponding date in text is being changed as 10/5/2018

but the result is not reflected in charts

Following is the image

Re: to write expression using variable

sakshikaul — Mon, 29 Oct 2018 07:27:20 GMT

Please help @Sunny Talwar

Re: to write expression using variable

Anil_Babu_Samineni — Mon, 29 Oct 2018 08:55:10 GMT

Are you sure that you have created variable for comparison purpose?

Sum({<lastDate = {"=$(=Date(date(lastDate) - $(compday)))"}>} [inv value])

compday - Can you please check if The variable is same. And Can you show the same variable in Text object after entering some value whether it is changing or not?

Re: to write expression using variable

sakshikaul — Mon, 29 Oct 2018 09:23:53 GMT

I have varaiable name DateOfComp labelled as

LastDate - No. Of Days

in which I have entered the value as follows :-

and comp is again a variable which has user defined value and it is used in the formula used for DateOfComp

and when expression is taken as an text box object the value is not changing for the following expression

Sum({<lastDate = {"=$(=Date(date(lastDate) - $(compday)))"}>} [inv value])

but when I enter compday=8 the corresponding value gets changed for the DateOfComp labelled as LastDate - No. Of Days

Re: to write expression using variable

Anil_Babu_Samineni — Mon, 29 Oct 2018 10:06:07 GMT

Can you test alone in text object using below and tell me what is returning?

=Date(date(lastDate) - $(compday))

Re: to write expression using variable

sakshikaul — Mon, 29 Oct 2018 10:20:00 GMT

Following I am getting in text box

Re: to write expression using variable

sakshikaul — Mon, 29 Oct 2018 12:18:15 GMT

Why this particular expression is giving an error?

=Sum({<sold_Date(=$(DaysOfComp))}>} [inv value])

Re: to write expression using variable

Anil_Babu_Samineni — Mon, 29 Oct 2018 12:19:30 GMT

Can you change compday and show the same?

Re: to write expression using variable

sakshikaul — Mon, 29 Oct 2018 12:32:59 GMT

if I am entering compday as 7 then I am getting value as 10/7/2017

Re: to write expression using variable

sakshikaul — Mon, 29 Oct 2018 12:34:08 GMT

here both the expression are giving me same values

=Sum({<sold_Date = {"=$(=Date(date(Sold_Date)))"}>} [inv value])

and

=Sum({<Sold_Date = {"=$(=Date(date(Sold_Date) - $(compday)))"}>} [inv value])

why?

Re: to write expression using variable

Anil_Babu_Samineni — Mon, 29 Oct 2018 12:58:18 GMT

Try this?

Sum({<sold_Date = {"=$(=Date(Max(sold_Date) - $(compday)))"}>} [inv value])

Re: to write expression using variable

sakshikaul — Mon, 29 Oct 2018 15:24:35 GMT

I want the variable which I am using here below in the image ie DaysOfcomp should be used in expression

so my expression will be this? This below expression is giving an error, Please help me in correcting this. Rest is done

=Sum({<lastDate = {=Date(DaysOfComp)}>} [inv value])

Re: to write expression using variable

Anil_Babu_Samineni — Tue, 30 Oct 2018 02:36:40 GMT

You said that DaysofComp is number which comes thru input box, And you are using Date format for that. Qlik can't identify as DD or MM or YYYY. Not really sure what you mean to first message and Different than Last Message. BTW, You expression Wrong - Check the set analysis rules here https://www.analyticsvidhya.com/blog/2014/01/set-analysis-qlikview/

And More over, You need to convert input box to Date then my suggestion would be this - https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/makedate.htm

If None of them are not working - We may ask input and output result which you want to see Here are the some tips

Uploading a Sample

Re: to write expression using variable

sakshikaul — Tue, 30 Oct 2018 04:36:45 GMT

DaysOfComp is not a number. It is in date format

value of DaysOfComp is calculated in following formula

=(lastDate)-compday

and when DaysOfcomp is taken as a text box its value is shoen below

Re: to write expression using variable

sakshikaul — Tue, 30 Oct 2018 06:48:19 GMT

Hi I am not able to see 'use advanced editor option' to upload application