syntax issue in set analysis

[toddbuss]

Can anyone tell me why this set analysis works:

Count( {$<ReportDate={"$(=(Max(ReportDate)))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])

and this one doesn't?:

Count( {$<ReportDate={"$(=(Max(ReportDate)-3))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])

The Qlik syntax is a necessary evil...but very evil.

Thanks.

[swuehl]

Assuming that your ReportDate format is e.g. 'M/D/YYYY' and that ReportDate field shows indeed a value like requested by the modifier (double check for example that you are not coping with timestamps instead of dates), you can try just formatting the modifier value:

Count( {$<ReportDate={"$(=Date(Max(ReportDate)-3,'M/D/YYYY'))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])

Dates in Set Analysis

[zebhashmi]

I think this should work

Count( {$<ReportDate={"$(=(=(Max(ReportDate)-3)))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])

[ger_alegria]

Try with:

Count( {$<ReportDate={"$(=(Max(num(ReportDate))-3))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])

[sunny_talwar]

This is a weird behavior which I have witnessed as well in the past... Lets look at this simple example

SET DateFormat='M/D/YYYY';

Table:

LOAD Date(MonthStart(Today() + IterNo()), 'YYYY-MM-DD') as MonthStart,

  Ceil(Rand() * 1000) as Sales

AutoGenerate 1

While IterNo() <= 1000;

Here you will see that the default date format is M/D/YYYY, but I have used YYYY-MM-DD for MonthStart. Now when I calculated Max(Monthstart) I see this

It keeps the format of Monthstart. But now if I do this -> =Max(MonthStart) - 3, I am seeing this

Now set analysis is very format sensitive and you need to make sure that format is not lost. So, in this case since the MonthStart is formatted as YYYY-MM-DD, we need to use Date function to convert it back to this format.

=Date(Max(MonthStart) - 3, 'YYYY-MM-DD')

[toddbuss]

Thank you all for your help on this.  I overestimated my skill and I simplified my code to spare everyone all the noise.  My formula still doesn't work.  With the added date formatting everyone suggested, my real-world code looks like this:

Count(DISTINCT

{<ReportDate =

{"$(='=' &

date(Max(ReportDate)-weekday(Max(ReportDate),4),'M/D/YYYY') )"},

[DC #]

= e({<ReportDate = {"$(='=' &

date(Max(ReportDate)-weekday(Max(ReportDate),4)-7),'M/D/YYYY')"}>})>}

[DC #])

//date(Max(ReportDate)-weekday(Max(ReportDate),4))  //  = latest friday

//date(Max(ReportDate)-weekday(Max(ReportDate),4)-7) //  = friday before

Sunny, you may recognize the origin of this problem.  :-).  The goal is to count the discrepancies between reports from two specific dates.   Later on, I hope to insert variables instead of dates, and then use recent fridays as default dates in the user prompts. 

[swuehl]

Why are you adding a equal sign before the date? This will indicate an advanced / expression search and is not what you want.

To debug your expression, put it as expression in a straight table without an expression label (and ReportDate as dimension). Now, when hovering over the label, you see the expression with all dollar sign expansions expanded.

What do you see?

[toddbuss]

Yes, Stefan, that was some leftover clutter that I thought was harmless.  Here's my attempt to clean it up:

Count(DISTINCT

{<ReportDate ={"$(date(Max(ReportDate)-weekday(Max(ReportDate),4),'M/D/YYYY') )"},[DC #]

= e(

{<ReportDate = {"$(date(Max(ReportDate)-weekday(Max(ReportDate),4)-7),'M/D/YYYY')"}>})>}[DC #]

)

//date(Max(ReportDate)-weekday(Max(ReportDate),4))  //last friday

//date(Max(ReportDate)-weekday(Max(ReportDate),4)-7) //friday before

I don't see anything on hover-over, and my result is now zero.

[sunny_talwar]

You do need an equal... but not 2 equals (is what Stefan might have wanted to say)

Count(DISTINCT {<ReportDate = {"$(=Date(Max(ReportDate)-weekday(Max(ReportDate),4),'M/D/YYYY') )"}, [DC #] = e({<ReportDate = {"$(=Date(Max(ReportDate)-weekday(Max(ReportDate),4)-7),'M/D/YYYY')"}>})>} [DC #])

[swuehl]

Ah, you are using Qliksense, the mouse over works in QlikView.

If you want to evaluate an expression in a dollar sign expansion, you need one equal sign:

ReportDate ={"$(=date(Max(ReportDate)-weekday(Max(ReportDate),4),'M/D/YYYY') )"}

I was talking about the '='&... part.