topic Re: Set analysis issues when using date variables in QlikView

Set analysis issues when using date variables

Tue, 28 Oct 2014 10:47:57 GMT

Pushing this barrow again. I have the below set analysis counting like instances under "CustID" over various months. I have set up variables vThisMonth(01/09/2014) and vLastMonth (01/08/2014) to do pretty much as is stated.

I've used these variables in "where" statements and all working ok. However, in the below set analysis it just won't recognise the dates. I've tried the below formats (as per suggestions)

Formula
=count({<Date={"$(=vThisMonth)"},CustID=P({<Date={"$(=vLastMonth)"}>}CustID)>}CustID)
Result
0

Formula
=count({<Date={"$(vThisMonth)"},CustID=P({<Date={"$(vLastMonth)"}>}CustID)>}CustID)
Result
0

Formula
=count({<Date={"=$(=vThisMonth)"},CustID=P({<Date={"=$(=vLastMonth)"}>}CustID)>}CustID)
Result
40095 (which happens to be every instance, non-distinct)

The only way I can get this to work is to manually add the date

Formula
=count({<Date={"01/09/2014"},CustID=P({<Date={"01/08/2014"}>}CustID)>}CustID)
Result
24 (Correct)

Please help!! This is doing my head in!! Thanks all

Re: Set analysis issues when using date variables

ali_hijazi — Tue, 28 Oct 2014 10:52:30 GMT

did you define vLastMonth and vThisMonth preceeded with an equal sign?

since your dates are formatted then do not use the $ in your set analysis

Re: Set analysis issues when using date variables

Tue, 28 Oct 2014 11:03:24 GMT

Thanks Ali

I tried

=count({1}{<Date={"vThisMonth"},CustID=P({<Date={"vLastMonth"}>}CustID)>}CustID)

Result was still 0

Re: Set analysis issues when using date variables

ali_hijazi — Tue, 28 Oct 2014 11:06:55 GMT

I suggest that you transform your date fields into numbers

num(DayStart(date_field))

then repeat your expression with $ and see

one more thing you need to define the vLastMonth and vThisMonth with an "=' in the beginning

Re: Re: Set analysis issues when using date variables

Peter_Cammaert — Tue, 28 Oct 2014 11:09:29 GMT

How about this:

=count({1}{<Date={"$(vThisMonth)"},CustID=P({<Date={"$(vLastMonth)"}>}CustID)>}CustID)

Simple text substitution before the set is calculated. Note that both these variables should contain a correct date, either numeric (e.g. 45678) or formatted ("1/09/2014")

Best,

Peter

Re: Re: Set analysis issues when using date variables

Tue, 28 Oct 2014 11:13:42 GMT

Thanks Peter. Yep, tried that one too. Still result=0

Re: Set analysis issues when using date variables

Tue, 28 Oct 2014 11:14:27 GMT

Hi,

try this

=Date#(count({<Date={"=$(=vThisMonth)"},CustID=P({<Date={"=$(=vLastMonth)"}>}CustID)>}CustID))

(or)

=num#(count({<Date={"=$(=vThisMonth)"},CustID=P({<Date={"=$(=vLastMonth)"}>}CustID)>}CustID))

Re: Set analysis issues when using date variables

simenkg — Tue, 28 Oct 2014 11:15:52 GMT

How about?

=count({<Date={"$(=Date(vThisMonth,'DD/MM/YYYY'))"},CustID=P({<Date={"$(=Date(vLastMonth,'DD/MM/YYYY'))"}>}CustID)>}CustID)

Re: Set analysis issues when using date variables

Tue, 28 Oct 2014 11:19:11 GMT

Thanks Rakesh, both got more than 0, a lot more. Unfotunately counted every line again (result=40095)

Re: Set analysis issues when using date variables

Peter_Cammaert — Tue, 28 Oct 2014 11:20:49 GMT

In your document; open Settings->Variable Overview and scroll down until you see the two culprits. What is their current value?

Peter

Re: Set analysis issues when using date variables

Tue, 28 Oct 2014 11:21:05 GMT

unfortunately back to 0 with this one. Thanks Simen

Re: Set analysis issues when using date variables

simenkg — Tue, 28 Oct 2014 11:25:33 GMT

Then this is a Data-problem, and not a Set analysis problem.

That expressions does exactly what you ask when I test it myself.

How do you define your variables?

Re: Set analysis issues when using date variables

Tue, 28 Oct 2014 11:25:37 GMT

vThisMonth = 01/09/2014

vLastMonth = 01/08/2014

Re: Set analysis issues when using date variables

Tue, 28 Oct 2014 11:29:57 GMT

SET vThisMonth = MakeDate(2014, NUM(Month(Today())-1,'00'),01); //Report Month

SET vLastMonth = MakeDate(2014, NUM(Month(Today())-2,'00'),01); //Previous Report Month

Re: Set analysis issues when using date variables

simenkg — Tue, 28 Oct 2014 11:36:54 GMT

try changing it to

let vThisMonth = MonthStart(Today()-1);

let vLastMonth = MonthStart(Addmonths(Today()-1,-1));

By using LET, the variable will have a value when you reload the script.

If this does not work i would use

let vThisMonth = Floor(MonthStart(Today()-1)));

let vLastMonth = Floor(MonthStart(Addmonths(Today()-1,-1)));

If that doesnt work, then would you mind pasting a value from your Date field? Do you perhaps use a timestamp in your Date field?

Re: Set analysis issues when using date variables

jpenuliar — Tue, 28 Oct 2014 11:48:46 GMT

I agree with Ali,

May I suggests also to try showing your Variables in text box to compare your actual variable value to what is really your expected value from your Table.This is how i usually debug my formulas.

Regards,

Re: Set analysis issues when using date variables

Tue, 28 Oct 2014 11:55:24 GMT

I trid that Simen. However caused too many problems with other setups, scripts etc. I had to change back to original.

The above value I entered are the Autogen results. so variables have those dates in that format (DD/MM/YYYY).

The source date date is as below (from table view preview)

Re: Set analysis issues when using date variables

simenkg — Tue, 28 Oct 2014 12:06:13 GMT

Then try this:

=count({<Date={"$(=$(vThisMonth))"},CustID=P({<Date={"$(=$(vLastMonth))"}>}CustID)>}CustID)

Re: Set analysis issues when using date variables

Tue, 28 Oct 2014 12:10:48 GMT

You Simen, are a legend, that worked prefectly!!!!

Thanks mate

To all others, thanks for your help as well.