Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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)
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.
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.