Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

implanet
New Contributor II

set analysis issue

Hello,

We try to figure out why our expression is not calculated :

=sum({1<SAP_Period = {$(VCurrentPeriod)}>} Amount_CashCumul)

where VCurrentPeriod is a variable and is : year(today())&'/'&num(month(today()),00)

Displaying $(VCurrentPeriod) is 2017/05 and SAP_Period is in format YYYY/MM

If we replace the expression by  =sum({1<SAP_Period = {'2017/05'}>} Amount_CashCumul) it is OK

Any idea why? Thank you.

11 Replies
vinieme12
Esteemed Contributor II

Re: set analysis issue

is your field SAP_PERIOD an actual date field? or text field?

try like below

VCurrentPeriod   =  DATE#(Date(today(),'YYYY/MM'),'YYYY/MM')

implanet
New Contributor II

Re: set analysis issue

The SAP_Period is a text field, it is a concatenation of 2 other fields :

SAP_Fiscal_Year&'/'&SAP_Fiscal_Period as SAP_Period

We also try to force with the text() function, same result.

antoniotiman
Honored Contributor III

Re: set analysis issue

Hi Antoine,

there's equal sign to the left of definition VCurrentPeriod ?

and in expression

SAP_Period = {'$(VCurrentPeriod)'}

Regards,

Antonio

ahaahaaha
Honored Contributor

Re: set analysis issue

Hi,

Try

=sum({1<SAP_Period = {$(=VCurrentPeriod)}>} Amount_CashCumul)


Regards,

Andrey

implanet
New Contributor II

Re: set analysis issue

If we put an equal sign, the variable is calculated and no longer in text even with the text() function (strange behavior).

Also tried the quote in the set analysis like you said, same result.

implanet
New Contributor II

Re: set analysis issue

I just did, nothing changed.

This issue makes me crazy ^^

I think we will change the '/' separator and put a point instead. In our locale, the point will be not interpreted as an operator.

antoniotiman
Honored Contributor III

Re: set analysis issue

You put the expression in the column of a chart and you see what it shows, like

zebhashmi
Valued Contributor

Re: set analysis issue

I was thinking Andrey Khoroneko formula will work.

then may be you can try

=sum({1<SAP_Period = {$(=Date('=VCurrentPeriod', YYYY,MM)}>} Amount_CashCumul)


or


=sum({1<SAP_Period = {$(=Date($(=VCurrentPeriod), YYYY,MM)}>} Amount_CashCumul)


Regards



implanet
New Contributor II

Re: set analysis issue

I tried to define the VCurrentPeriod variable as a date like Vineeth suggested

VCurrentPeriod   =  DATE#(Date(today(),'YYYY/MM'),'YYYY/MM')

And then in the expression, interpreting SAP_Period as a date like:

=sum({1<date#(SAP_Period,'YYYY/MM') = {$(=VCurrentPeriod)}>} Amount_CashCumul)

This is not working.

So I reverse to the variable VCurrentPeriod   =  year(today())&'/'&num(month(today()),00) and I tried your expressions. Not working too.

Honestly since we have been working with qliksense (more than 6 months now), we are still confronted to this kind of situation, which is very frustating. Do you know if a debug mode will be implemented for the chart mode?

Community Browser