topic Re: Question about date in set analyst with variable in QlikView

Question about date in set analyst with variable

uacg0009 — Fri, 13 May 2016 05:55:37 GMT

Hi All,

I have data :

And I have a variable = today()

I just want to calculate that count(id) before the variable(today).

I want to make it using set analyst not if else.

So I try to use =Count({<DATE={"<=Variable1"}>}ID), but it shows 0.

I think it's a easy request, but i don't know why, maybe it's because of type.

Could you please help?

Thanks.

Aiolos Zhao

Re: Question about date in set analyst with variable

tamilarasu — Fri, 13 May 2016 05:56:56 GMT

Aiolos,

You missed to add $ Symbol. Try like below,

=Count({<DATE={"<=$(Variable1)"}>}ID)

Re: Question about date in set analyst with variable

PrashantSangle — Fri, 13 May 2016 05:58:36 GMT

Hi,

try like

Count({<DATE={"<=$(Variable1)"}>}ID)

Count({<DATE={"<=$(=Variable1)"}>}ID)

Regards

Re: Question about date in set analyst with variable

sujeetsingh — Fri, 13 May 2016 06:22:01 GMT

go through the document provided by Qlik on set analysis.

Set Analysis: syntaxes, examples

Re: Question about date in set analyst with variable

qlikview979 — Fri, 13 May 2016 06:42:59 GMT

Hi,

Try like this

Count({<DATE={"<=$(Variable1)"}>}ID)

Re: Question about date in set analyst with variable

uacg0009 — Fri, 13 May 2016 08:18:15 GMT

Thanks for reply, but could you please see my new attached, it's a little strange for my example, I don't know why.

Thanks.

Aiolos Zhao !

Re: Question about date in set analyst with variable

uacg0009 — Fri, 13 May 2016 08:20:10 GMT

Thanks for reply, but could you please see my new attached, it's a little strange for my example, I don't know why.

Thanks.

Aiolos Zhao

Re: Question about date in set analyst with variable

uacg0009 — Fri, 13 May 2016 08:22:44 GMT

Thanks for reply, but could you please see my new attached, it's a little strange for my example, I don't know why.

Thanks.

Aiolos Zhao

Re: Question about date in set analyst with variable

uacg0009 — Fri, 13 May 2016 08:25:25 GMT

Thanks for reply, but could you please see my new attached, it's a little strange for my example, I don't know why.

Thanks.

Aiolos Zhao

Re: Question about date in set analyst with variable

sunny_talwar — Fri, 13 May 2016 08:27:57 GMT

Try this:

=Count({<delivery_date={"<=$(=Date($(Variable1), 'YYYY-MM-DD'))"}>}id)

UPDATE: I think the issue is related to the formatting here. vVarable1 is in number format and in order for set analysis to work, it needs to be converted to a very specific date format -> YYYY-MM-DD which matches the date format for delivery_date. Read more about dates in set analysis here: Dates in Set Analysis

Re: Question about date in set analyst with variable

tamilarasu — Fri, 13 May 2016 08:37:50 GMT

Either change the variable like

Variable1

=Date(today()-5,'YYYY-MM-DD')

Or Change expression like

=Count({<delivery_date={"<$(=Date($(Variable1),'YYYY-MM-DD'))"}>}id)

Re: Question about date in set analyst with variable

uacg0009 — Fri, 13 May 2016 08:50:57 GMT

Hi Sunny,

Thanks for reply, it works now.

I also think that is because of the date type, so I tried <=date($(variable)), but it's wrong.

Anyway, thanks for your help. It bothered me because I think it's a some issue and strange.

Aiolos Zhao

Re: Question about date in set analyst with variable

uacg0009 — Fri, 13 May 2016 08:51:57 GMT

Hi Tamil,

Thanks for your help, it's also a good method to change the variable defination.

Thanks.

Aiolos Zhao