topic Re: Limit by two dimentions in an expression in QlikView

Limit by two dimentions in an expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

New to QV so I hope I worded this question correctly -

I want to limit sales by month. (I have a fat calendar table that holds all possible days, weeks, quarters, months, years for the period)

This works:

=sum({$<month_no_id = {$(#=Only(month_no_id ))},day=,calendar_date=, week_no_id=,week_no=>} sale_cost_value)

Each sale can have a different no of sales charges, and I need to limit by one of the charges within the month

This works to limit the sales type

- sum({<sales_cost_id={ '4'}>} [sales_cost_value]))

But something like this doesn't

sum({$<month_no_id = {$(#=Only(month_no_id ))},day=,calendar_date=, week_no_id=,week_no=>} sum({<sales_cost_id={ '4'}>} [sales_cost_value]))

Is there any way I can combine both restrictions in an expression or is there another way?

adamdavi3s — Thu, 16 Feb 2017 13:36:59 GMT

Hi Stefan,

SOmething like:

sum({$<month_no_id = {$(#=Only(month_no_id ))},day=,calendar_date=, week_no_id=,week_no=,sales_cost_id={ '4'}>} [sales_cost_value])

should do the trick

Thu, 16 Feb 2017 14:00:22 GMT

Thanks very much Adam, that issue killed my morning! Very much appreciated.

adamdavi3s — Thu, 16 Feb 2017 14:02:53 GMT

Hi Stefan,

Not a problem at all, and don't worry, Set Analysis kills most of my time at the moment!

Kind regards

Adam