Straight table with dates from variable not from selection

[ianwilson]

Hi,

I have a straight table with a dimension based on a date variable:

=if(Date>=($(varViewDate)-7) and Date<=($(varViewDate)-1), Date)

This shows 1 row for each of the 7 days before the date in the variable. Eventually the variable will be today() and will show the 7 days data before today, but I am using a variable because the test data isn't current.

The expressions in the straight table are as simple as you can get:

=Revenue

=Budget

with no summation or interpretation of the data required.

The problem I have is unlinking this information from any date selections. In the same application users will be able to perform analysis based on date selections, but I don't want this table to be affected by those date selections.

However I do want the users to be able to filter on location, so I cannot detach the table to fix the problem.

I thought using set analysis with the same expression as used in the dimension would work, but I just get "Errors in expression" because Only is not what I actually want. I tried the following:

=Only({1 <if(Date>=($(varViewDate)-7) and Date<=($(varViewDate)-1)} > Revenue)

OR

=Only( {1 < Date={($(varViewDate)-7),($(varViewDate)-6),($(varViewDate)-5),($(varViewDate)-4),($(varViewDate)-3),($(varViewDate)-2),($(varViewDate)-1)}>} Revenue)

It's occurred to me this might be possible without set analysis, but using the {1} Total set seems the obvious solution. I probably need a different function but most examples use the sum function, which does seem necessary when I only want 1 value, rather than the sum of values.

My QlikView journey is just starting, so any help appreciated. I'm also waiting for my developer license, so am still using a personal edition at the moment.

Thanks

Ian

[Anonymous]

HI

If you are looking for result IRRESPECTIVE of selection , please go like below

=ONLY({1<Date={">=$(=(=Date(varViewDate-7,'MM/DD/YYYY'))<=$(=Date(varViewDate,'MM/DD/YYYY'))"}>} Revenue)

@Ian highlighte in blue : please modify according to you Date field format DD/MM or YYY/DD etc.,

I think above should give Revenue

if you looking for sum ??

=SUM({1<Date={">=$(=(=Date(varViewDate-7,'MM/DD/YYYY'))<=$(=Date(varViewDate,'MM/DD/YYYY'))"}>} Revenue)

[Anonymous]

Hi Ian,

Also please go through this

Set Analysis for certain Point in Time

[johnw · Accepted solution]

He wants to be able to filter by location, and I assume by any non-date-related fields. So as a modification to your expression, I think we'll need to ignore all date-related fields rather than use {1}. So if we have Year, Month, and Week in our calendar along with the Date, perhaps something like this:

only({<Year=,Month=,Week=,Date={">=$(=(=Date(varViewDate-7,'MM/DD/YYYY'))<=$(=Date(varViewDate,'MM/DD/YYYY'))"}>} Revenue)

[ianwilson]

Hi Allu and John

Thanks for the quick response. I've tried the following variations on the suggestions, but not got to the field exclusions yet - need to get the basic set working first.

None of these other than the basic "=Revenue" returns any values.

//=Revenue
//
//=Only({1<Date={">=$(=(=Date(varViewDate-7,'DD/MM/YYYY'))<=$(=Date(varViewDate-1,'DD/MM/YYYY'))"}>}Revenue)
//
//=ONLY({1<Date={">=$(=(=Date(varViewDate-7,'DD/MM/YYYY'))<=$(=Date(varViewDate,'DD/MM/YYYY'))"}>}Revenue)
//
//=ONLY({1<Date={">=$(varViewDate-7)<=$(varViewDate-1)"}>}Revenue)
//
//=ONLY({1<Date={">=($(varViewDate)-7)<=($(varViewDate)-1)"}>}Revenue)
//
//=ONLY({1<Date={">=(=Date(($(varViewDate)-7),'DD/MM/YYYY'))<=(=Date(($(varViewDate)-1),'DD/MM/YYYY'))"}>}Revenue)

//=ONLY({1<Date={">=(=Date($(varViewDate)-7))<=(=Date($(varViewDate)-1))"}>}Revenue)

//=ONLY({1<Date={">=Date(($(varViewDate)-7),'DD/MM/YYYY')<=Date(($(varViewDate)-1),'DD/MM/YYYY')"}>}Revenue)

=ONLY({1<Date={">=Date($(varViewDate)-7)<=Date($(varViewDate)-1)"}>}Revenue)

After some testing with text fields I found the following expressions all return the correct date in DD/MM/YYYY or in the correct Number value i.e. 42523

$(=(=Date(varViewDate-7,'DD/MM/YYYY'))

Date(($(varViewDate)-7),'DD/MM/YYYY')

$(varViewDate)-7)

Date($(varViewDate)-7)

So I don't think I have an issue with the date formats, having tested them in text fields and tried all 4 different ways of writing them.

The Set expression to select the date with the >= and <= makes perfect sense to me, but I still don't get any values in the Revenue field:

I haven't applied set analysis to the other fields yet so they are still displaying data with a basic =Budget type expression. The date field is just for troubleshooting at this time.

I'm going to have a read of the suggestion below Set Analysis for certain Point in Time

Thanks for the help.

[ianwilson]

Hi

I now realise I didn't understand the difference between the Total Set for a specific date range and the Current Set excluding dates .  After sleeping on it and rereading the posts I realised my mistake.

The solution is the expression:

=ONLY({$<Year=,Month=,Week=,Date={'>=$(=(varViewDate)-7)<=$(=(varViewDate)-1)'}>}Revenue)

Which takes the revenue for the current selection, but only for the dates in the range of the variable, ignoring any date selection. ( Obviously! ).

This may have an issue if I have duplicate values for Revenue in this range - need to check - and I need to check out the impact of the Date formatting, as it is probably not best practice to have a bare variable in a date expression, but it works and I understand why.

Thanks for the help - much appreciated.