topic Re: set analysis for older data in QlikView

set analysis for older data

Thu, 02 Jul 2015 13:27:21 GMT

Hi,

I have a book where the data is basically historic, so the date (can be 1-2 months) or longer is permanently ON as filter. It works ok for one day.

I would like to have a query on the number of items as per yesterday so I am using something like this

count({1<Position01.BusinessDate = {'$(=date($(vPreviousBusinessDate)))'},Position01.IsOtc = {1}> } %Key_BusinessDate_AccountCode_InstrumentID )

This statement correctly gives me the number of items for yesterday. However, because I am using the count({1 I think it now ignores the current selection and gives me totals as per the above condition but ignoring any current selection I may have. Is there a way to get the data I need but for the previous day but maintaining my current selection? If I use the $ instead of 1 then I get the current selection but not sure I can access the previous day's data. Any ideas how I can achieve this?

Thank you!

Panos.

Re: set analysis for older data

sinanozdemir — Thu, 02 Jul 2015 13:34:59 GMT

Have you tried the below?

count({1<Position01.BusinessDate = {'$(=date($(vPreviousBusinessDate)))'} + Position01.IsOtc = {$}> } %Key_BusinessDate_AccountCode_InstrumentID )

Re: set analysis for older data

Gysbert_Wassenaar — Thu, 02 Jul 2015 13:35:04 GMT

Position01.BusinessDate = {'$(=date($(vPreviousBusinessDate)))'} will overrule the current selections for the Position01.BusinessDate field regardless of the set identifier $ or 1 you use. If you don't have selections in other date related field that influence the possible values in Position01.BusinessDate then you don't need to do anything else. Suppose you also have a field Position01.BusinessMonth and you did make a selection in that field. Then you can overrule that selection with Position01.BusinessMonth=:

count({<Position01.BusinessMonth=, Position01.BusinessDate = {'$(=date($(vPreviousBusinessDate)))'},Position01.IsOtc = {1}> } %Key_BusinessDate_AccountCode_InstrumentID )

Re: set analysis for older data

Thu, 02 Jul 2015 13:53:21 GMT

Gysbert, not sure what you mean by Position01.BusinessMonth. I would like to include the current filters dynamically in my set analysis condition (for previous date) if possible or good idea.

My problem is that I can't apply the User Filters (Current Selection) when I try to get data for the previous days. At the moment if a user selects say Counterparty then Counterparty will be ignored as a filter for the previous day count(). It will be a filter for the current day but the previous day only has BusinessDate & IsOtc as filters.

Thank you.

Re: set analysis for older data

Thu, 02 Jul 2015 14:41:04 GMT

I see what you mean now by Position01.BusinessMonth, you can exclude fields that you don't need in the condition. Let me try again, seems to work...