topic Re: Using a variable storing Date in Set Analysis in QlikView

Using a variable storing Date in Set Analysis

Tue, 22 Apr 2014 11:34:55 GMT

Hi!

I´m having a problem....

I´ve got several Date fields in my Data and I need to be able to select one of those dates based in a variable that is storing another date.

Date fields:

-Data;

-dataVenda.

Variable:

-vSetData =dataVenda

This variable captures date from "dataVenda". Used in a Text Box it returns the Date just fine. Text box, i´m using =vSetData

Now my problem is that I cannot use vSetData in Set Analysis... I´ve tried several expressions, and none of them worked.

My expression is plain simple, just a sum with set analysis:

Sum({$<Data={???????}>} Afield)

If I use, for instance, Sum({$<Data={'26-09-2012'}>} Afield) it works fine.

I´ve used $(vSetData), =vSetData, some expressions with '=$(=vSetData)', I don´t know how many expressions have I tried... 😕

I need to use Data, because is the one directly related to the field I need to Sum.... I cannot use dataVenda directly in here...

Best Regards,

Bruno Silva.

Re: Using a variable storing Date in Set Analysis

MK_QSL — Tue, 22 Apr 2014 11:36:36 GMT

vSetData =Date(dataVenda)

Sum({$<Data={'$(vSetData )'}>} Afield)

Re: Using a variable storing Date in Set Analysis

jonathandienst — Tue, 22 Apr 2014 11:41:29 GMT

Does the text box with =vSetData return anything when you clear all selections? If the answer is no, then you cannot use vSetData in the way you are attempting. If you need to use all the possible values (after making selections) of dataVenda, then you could try:

Sum(<Data = P(dataVenda)>} Afield)

HTH

Jonathan

Re: Using a variable storing Date in Set Analysis

Tue, 22 Apr 2014 12:47:08 GMT

Hi Jonathan!

I wasn´t looking for All Possible values... But thanks anyway!!! Manish gave the right answer... I knew that it was a minor detail...

Thank you both!!!

Best Regards,

Bruno Silva.

Re: Using a variable storing Date in Set Analysis

Tue, 22 Apr 2014 13:43:53 GMT

Why doesn´t this work the same way when I create a new variable to Last Year same date:

vSetDataLY =AddMonths( dataVenda, -12)

And then use it as I did before?

Sum({$<Data={'$(vSetDataLY )'}>} Afield)

This doesn´t work... But in a Textbox, It does return the correct date with =vSetDataLY

Best regards,

Bruno Silva.

Re: Using a variable storing Date in Set Analysis

MK_QSL — Tue, 22 Apr 2014 13:55:30 GMT

Try

vSetDataLY =Date(AddMonths( dataVenda, -12))

Re: Using a variable storing Date in Set Analysis

Tue, 22 Apr 2014 14:08:11 GMT

Didn´t work....

As the Text Box returns correct date, I´m guessing that is something about how the variable is implemented in Set Analysis... But again, tried several ways to call the vSetDataLY in Data=, but any results so far...

Thanks,

BS.

Re: Using a variable storing Date in Set Analysis

MK_QSL — Tue, 22 Apr 2014 14:26:49 GMT

Try

vSetDataLY = Date(AddYears(dataVenda,-1),'DD-MM-YYYY')

Re: Using a variable storing Date in Set Analysis

Tue, 22 Apr 2014 14:32:57 GMT

It doesn´t work either...

But the Text Box is still returning the right date...

Re: Using a variable storing Date in Set Analysis

MK_QSL — Tue, 22 Apr 2014 14:36:36 GMT

what is the format of Data?

Re: Using a variable storing Date in Set Analysis

Tue, 22 Apr 2014 14:44:59 GMT

It´s a Date also... DD-MM-YYYY. And that worked fine in the solution you provide before... So I thought it was just switch the variable name and it would be working...

I don´t understand why it does return correct date in text box as in previous problem, and it does not work using same syntax, but with different variable.

Re: Using a variable storing Date in Set Analysis

MK_QSL — Tue, 22 Apr 2014 14:47:46 GMT

vSetDataLY = Date(AddYears(Max(dataVenda),-1),'DD-MM-YYYY')

Sum({$<Data={'$(=vSetDataLY )'}>}Afield)

Re: Using a variable storing Date in Set Analysis

Tue, 22 Apr 2014 14:59:45 GMT

Again... No results.... Sorry...

It doesn´t work like this:

Data={'$(=vSetDataLY )'}

Neither like this:

Data={'$(vSetDataLY )'}

And again the Text Box return correct date!

Re: Using a variable storing Date in Set Analysis

MK_QSL — Tue, 22 Apr 2014 15:02:45 GMT

I need to stop guessing. Could you please upload your sample apps or sample data?

Re: Using a variable storing Date in Set Analysis

Tue, 22 Apr 2014 15:06:23 GMT

LOL... You´re right! I will try to do that... as soon as I can.... Just got another issue from another client in hands that I need to solve.

Thank you!

Re: Using a variable storing Date in Set Analysis

Tue, 22 Apr 2014 17:18:02 GMT

Hello again...

Here is the sample of the qvw in which I´m trying to implement the expression we´ve been talking about.

Thank you very much for your help!!!

Best Regards,

BS.

teste.qvw

EDIT - Forgot to tell you that you can test with date 26-Sep-2013 / 26-Sep-2012... In this one I´m sure that is data to show.

Re: Using a variable storing Date in Set Analysis

MK_QSL — Tue, 22 Apr 2014 17:32:44 GMT

Do one thing.. .Select any Date from Date Selector...

Now if you dont get any data for previous year....

Try to find out that you indeed have data for that particular date (previous Year)

If you select 28-12-2013 there should be some data for 28-12-2012

Re: Using a variable storing Date in Set Analysis

Wed, 23 Apr 2014 14:15:09 GMT

Hi Manish.

In my example above, it doesn´t return any data to previous year, although I´m absolutly sure that data exists for that day... For instance, the date you suggested has data in both year dates.... but it doesn´t show it in previous year... The Qvw example wasn´t helpful to get to any conclusion?

Thanks,

BS.

Re: Using a variable storing Date in Set Analysis

Wed, 23 Apr 2014 14:37:56 GMT

Just Solve it!

As I said before, I knew that it must be a minor detail... Don´t know why, but it only works with a 1 instead of $:

sum({1<Data={'$(vSetDataLY)'}>} Afield)

Thank you Manish for all your help and patience...

Best Regards,

Bruno Silva.