topic Re: Calendar selection Set Analysis in QlikView

Calendar selection Set Analysis

marco_puccetti — Thu, 23 Apr 2015 10:36:59 GMT

Hello, i need to create a table in which the selection (the dimension field) would be made in function of a calendar selection, is correct this expression?

={<ABI=, DATA_ESTRAZIONE_BANCA = {"=$(=DT_RIFERIMENTO)"}>}

In this expression i intend to extract all the ABI for a "DATA_ESTRAZIONE_BANCA" equal to the "DT_RIFERIMENTO" selection (that is connected to the calendar control).

In the editor the expression is marked wrong.

Can you tell me the correct syntax if this is wrong?

Thanks

Marco

Re: Calendar selection Set Analysis

ankitaag — Thu, 23 Apr 2015 10:59:27 GMT

Can you write the full expression

Re: Calendar selection Set Analysis

marco_puccetti — Thu, 23 Apr 2015 11:05:23 GMT

This is the full expression i need to use in the dimension dialog below.

Thanks

Marco

Re: Calendar selection Set Analysis

jonathandienst — Thu, 23 Apr 2015 11:12:57 GMT

If that is your full expression then it is invalid - this is just the set filter. Also your naked reference to DT_RIFERIMENTO will return null if more than one value is possible. If this comparison is on a row by row basis, then you cannot use a set expression anyway.

Assuming that it not a row by row comparison and that the date format of DATA_ESTRAZIONE_BANCA and DT_RIFERIMENTO are the same, then you would need something like this:

=Count({<DATA_ESTRAZIONE_BANCA = P(DT_RIFERIMENTO)>} Distinct ABI)

Or Sum(...) if that is what is required, or possibly without the "Distinct".

HTH

Jonathan

Edit == I see you want a calculated dimension. See next post

Re: Calendar selection Set Analysis

jonathandienst — Thu, 23 Apr 2015 11:15:42 GMT

For a calc dimension containing the matching values of ABI, then possible this (depends on your data model):

=Aggr(Only({<DATA_ESTRAZIONE_BANCA = P(DT_RIFERIMENTO)>} ABI), ABI)

Re: Calendar selection Set Analysis

ankitaag — Thu, 23 Apr 2015 11:19:41 GMT

Yes,

That's what I was trying to say that in the starting of the expression you need to write an aggregated function to use the set analysis and at the end need to mention a field name.

Re: Calendar selection Set Analysis

prajuds99 — Thu, 23 Apr 2015 11:23:32 GMT

Hi Marco,

If I understand correctly there will be some user selection in field DT_RIFERIMENTO and you want to filter DATA_ESTRAZIONE_BANCA based on that selection.I think below expression would work

Your expression:

{<ABI=, DATA_ESTRAZIONE_BANCA = {"$(=GetFieldSelections(DT_RIFERIMENTO))"}>}

Expression which I tried:

=sum({<DMANUFACTURER={"$(=GetFieldSelections(MANUFACTURER))"}>}[SALES DOLLARS])

Re: Calendar selection Set Analysis

Thu, 23 Apr 2015 11:28:42 GMT

yes ankita said correct you need to do something with aggregated function to use the set analysis. But there are few basic principal before implement set analysis in calendar section as below

Firstly a standard Calendar is created using AutoGenerate. I also take the opportunity to incorporate Business Day flags (set by loading regional public holidays).

A Cartesian Product is then produced by left Joining Distinct Dates with itself without the key. This gives us all the possible date
combinations.

I then create the flags; Firstly Point In Time Flags such as Today, This Week, This Month, etc. Then period flags such as Previous Day, Previous Month, Previous Month to Date, etc.

Update - Following the comments I've added more code to the CLEAN UP tab to remove any lines where no flags have been set and any lines where the Possible Date is higher than the selected date which reduces the tables size by over 50%

Update - Fixed a bug resulting from the previous update

Update 22-APR-2015 - Fixed a bug which reduces the number of rows in the final calendar

I hope this of some use to people, I would love to hear your comments and further suggestions.

Re: Calendar selection Set Analysis

marco_puccetti — Thu, 23 Apr 2015 12:31:37 GMT

I don't need a sum i need a list of IBAN related to the DATA_ESTRAZIONE_BANCA field.

Is it possible to obtain a formula in order to have this?

Thanks

Marco

Re: Calendar selection Set Analysis

marco_puccetti — Thu, 23 Apr 2015 13:02:17 GMT

I've decided to change the approach and take the data from the date table field, although there aren't all the calendar days, but a subset of them.

Thanks

Marco