topic Re: issue in expression in QlikView

issue in expression

Chanty4u — Sat, 16 Nov 2024 21:31:06 GMT

i have one variable like below.

=vPrevQtr

=chr(39)&concat(DISTINCT(Left(Quarter,4)-1)&Right(Quarter,2),chr(39)&','&chr(39)) & chr(39)

i have used vPrevQtr in text object its working fine its giving exact result.. but same varibale used in below expression its giving null. As we have data for that [its shwoing Expression is OK ]

if(GetFieldSelections(ServiceGroup) = '9R', Sum({$<Quarter={$(vPrevQtr)},Type={'Actuals'}>}Service_Amount),
if(WildMatch(getfieldselections(ServiceGroup),'*9R*'),Sum({$<Quarter={$(vPrevQtr)},Type={'Actuals','SKU'}>}Service_Amount),
Sum({$<Quarter={$(vPrevQtr)},Type={'Actuals'}>}Service_Amount)))

where im doing wrong>?

Thanks

Re: issue in expression

vishsaggi — Mon, 28 Jan 2019 21:00:17 GMT

Can you try quotes around your variable like {'$(vPrevQtr)'} ?

Re: issue in expression

vikramv — Tue, 29 Jan 2019 00:29:17 GMT

Try to replace this Quarter={$(vPrevQtr)} with Quarter=$(vPrevQtr) in set analysis.

As you already included the brackets and quotes in a variable ?

Re: issue in expression

sunny_talwar — Tue, 29 Jan 2019 01:01:33 GMT

@Chanty4u wrote:
Hi all, @sunny_talwar

i have one variable like below.
=vPrevQtr
=chr(39)&concat(DISTINCT(Left(Quarter,4)-1)&Right(Quarter,2),chr(39)&','&chr(39)) & chr(39)

i have used vPrevQtr in text object its working fine its giving exact result..

When you say it works in text box object, what exactly do you mean? Can you show an image of what you see in a text box object when you use this?

Re: issue in expression

Chanty4u — Tue, 29 Jan 2019 05:23:18 GMT

Vikkyv getting error in set modifier adhoc element list ',' ) expected

Re: issue in expression

Chanty4u — Tue, 29 Jan 2019 05:22:19 GMT

Vishsaggi am getting 0

Re: issue in expression

Chanty4u — Tue, 29 Jan 2019 05:08:56 GMT

Hi @sunny_talwar when i select in Quarter field 2019Q1 and 2019Q2 the text box result is 2018Q1,2018Q2

if i select 2019Q1 result is getting 2018Q1 ...like that

Re: issue in expression

HirisH_V7 — Tue, 29 Jan 2019 07:24:06 GMT

Hi Suresh,

i just checked its working fine and picking the variables as it should. Even i created an example for the same. Below is screen shot for ref.

i Guess there is some issue pertaining with other data parameters. Try removing them and check quarter alone first. attached is the sample qvw for ref.

HTH,

HirisH

Re: issue in expression

rubenmarin — Tue, 29 Jan 2019 07:45:18 GMT

Hi Suresh, I think your variable needs an starting equal sign:
SET vPrevQtr = =chr(39)&concat(DISTINCT(Left(Quarter,4)-1)&Right(Quarter,2),chr(39)&','&chr(39)) & chr(39);

If it's not possible to add the starting equal sign, maybe using the variable as:
Sum({$<Quarter={$(=$(vPrevQtr))}>}Quarter)

Re: issue in expression

HirisH_V7 — Tue, 29 Jan 2019 07:52:37 GMT

Check this with Half year and year updated.

PFA

Re: issue in expression

Chanty4u — Tue, 29 Jan 2019 08:11:07 GMT

added = in the variable and used ur expression also same "0 " am getting the result.

Re: issue in expression

rubenmarin — Tue, 29 Jan 2019 08:20:21 GMT

In that case I will go from simpler expression to more complex ones, and checking when the value is changing to zero...

First try:
Sum(Service_Amount)

2nd try (If first try it's not zero):
Sum({$<Quarter={$(vPrevQtr)}>}Service_Amount)

3rd try (If 2nd try it'0s not zero):
Sum({$<Quarter={$(vPrevQtr)},Type={'Actuals'}>}Service_Amount)

This is to isolate the set analysis that is removing access to data, once isolated you can focus in that step to solve the problem.

Re: issue in expression

Chanty4u — Tue, 29 Jan 2019 08:22:25 GMT

Hi Rubenmarin

sample data is attached . and script is used to create year and halfyear,quarter

Data:

load Distinct
Quarter,
left(Quarter,4) as Year,
If(Right(Quarter, 1) < 3, LEFT(Quarter,4) & 'H1', LEFT(Quarter,4) & 'H2') as Half_Year;

Text box expresion:

=if(GetFieldSelections(ServiceGroup) = '9R', Sum({$<Quarter={$(=vPrevQtr)},Type={'Actuals'}>}Service_Amount),
if(WildMatch(getfieldselections(ServiceGroup),'*9R*'),Sum({$<Quarter={$(=vPrevQtr)},Type={'Actuals','SKU'}>}Service_Amount),
Sum({$<Quarter={$(=vPrevQtr)},Type={'Actuals'}>}Service_Amount)))

Filter selection in front end:

Half year only.

Re: issue in expression

Chanty4u — Tue, 29 Jan 2019 16:11:18 GMT

i Rubenmarin

i have followed your 3 steps still 0 you can see attached screenshot

Re: issue in expression

rubenmarin — Tue, 29 Jan 2019 08:35:01 GMT

Ok, I haven't read about the half_year... that's because selections in half_year is from current year, and quarter set analysis is on last year, so there are no values with both conditions, try removing selection of half_year in set analysis:
=if(GetFieldSelections(ServiceGroup) = '9R', Sum({$<Half_Year, Year, Quarter={$(=vPrevQtr)},Type={'Actuals'}>}Service_Amount),
if(WildMatch(getfieldselections(ServiceGroup),'*9R*'),Sum({$<Half_Year, Year, Quarter={$(=vPrevQtr)},Type={'Actuals','SKU'}>}Service_Amount),
Sum({$<Half_Year, Year, Quarter={$(=vPrevQtr)},Type={'Actuals'}>}Service_Amount)))

Edit: Same for Year

Re: issue in expression

HirisH_V7 — Tue, 29 Jan 2019 08:42:46 GMT

Check the attached.

Re: issue in expression

Chanty4u — Tue, 29 Jan 2019 08:54:22 GMT

thanks. im getting some values now. i need to implement this into other 4,5 variables and validate the data and come back to you.