topic Set analysis based on date selection in QlikView

Set analysis based on date selection

swatiras — Sat, 16 Nov 2024 04:45:03 GMT

Hi , I am trying to sum up monthly figures, based on list-box selection.

This is the expression i used :

=Sum({$<DT_MONTHYEAR ={">=$(=Date(max([DT_MONTHYEAR])))"}>}AMOUNT)

This one works fine , but unable to write and expression to give AMOUNT sum for previous MonthYear(previous to the one currently selected)

jyothish8807 — Wed, 16 Jan 2019 06:23:01 GMT

Hi Try this:

Assuming you have a Year field in your data model.

=Sum({$<DT_MONTHYEAR ={">=$(=Date(max([DT_MONTHYEAR])))"},Year ={"=$(=Year(max([DT_MONTHYEAR]))-1)"}>}AMOUNT)

jonathandienst — Wed, 16 Jan 2019 06:37:09 GMT

Like this:

=Sum({$<DT_MONTHYEAR ={">=$(=AddMonths(Max([DT_MONTHYEAR]), -1))"}>} AMOUNT)

You may need to override selections if you are making date selections in another field. Example:

=Sum({$<DT_MONTHYEAR ={">=$(=AddMonths(Max([DT_MONTHYEAR]), -1))"}, Year, Month>} AMOUNT)

swatiras — Thu, 17 Jan 2019 05:02:52 GMT

Another issue :

Lets say i want to find out the average based upon the number of days in the month.

I need a set analysis expression like :

=Sum({$<MONTHYEAR ={">=$(=Date(max([MONTHYEAR])))"}>}AMOUNT)/divide by the number of days in the MONTHYEAR selected in the 'MONTHYEAR' listbox