topic Re: Use dimension that is not linked to Facts Table in QlikView

Use dimension that is not linked to Facts Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

I've attached a sample app.

I want to use, dimension AllMonth, from table cal, which is not linked to fact table.

In chart I want to use the dimension AllMonth from cal to sum(Amount)

I want that when:

- AllMonth = 1, sum(Amount) from fact table where Month = 1 (it should be equal to 700)

- And when AllMonth = 2, sum(Amount) from fact table where Month = 2 (it should be equal to 300)

How can I do this ?

Thanks in advanced,

Sílvia

Re: Use dimension that is not linked to Facts Table

swuehl — Tue, 24 Jan 2017 11:33:26 GMT

Maybe use AllMonth as dimension and as expression

=Pick(AllMonth,

YourFirstExpressionToEqual700,

YourSecondExpressionToEqual300

)

Adapt the two expressions as needed.

Re: Use dimension that is not linked to Facts Table

tracysmart — Tue, 24 Jan 2017 11:43:00 GMT

perhaps use the following expression to link the dimension to the data island.

sum(if(AllMonth=Month, Amount)

Re: Use dimension that is not linked to Facts Table

Anonymous — Tue, 24 Jan 2017 14:40:27 GMT

Hi Tracy,

Thanks for your solution, it works.

But know I'm trying to do this in my app, using the next expression:

Sum(aggr(

Count(

if(actual_date_from >= Date(Min([DateMasterCal.autoCalendar.YearMonth]))

and actual_date_from <= Date(Max([DateMasterCal.autoCalendar.YearMonth]))

and actual_date_to >= Date(Min([DateMasterCal.autoCalendar.YearMonth]))

and actual_date_to <= Date(Max([DateMasterCal.autoCalendar.YearMonth])),

order_code

)

), [DateMasterCal.autoCalendar.YearMonth]))

Notice that in this expression, actual_date_from and actual_date_to represents my field Month, and DateMasterCal.autoCalendar.YearMonth represents AllMonth.

The dimension in my chart is DateMasterCal.autoCalendar.YearMonth.

So I want that when bar corresponds to, for example, 2016-Sep, the min and max date of DateMasterCal.autoCalendar.YearMonth are, respectively, 01-09-2016 and 30-09-2016.

So I only want order_code where actual_date_from and actual_date_to are inside this range.

But this expression is not giving any results.

Can you tell what I'm doing wrong?

Thanks,

Sílvia

Re: Use dimension that is not linked to Facts Table

tracysmart — Wed, 25 Jan 2017 08:20:33 GMT

Hi Silvia

I am not sure I am following you here.

If you have a calendar table associated to your Orders on a field such as order date, and you build a chart with the YearMonth as a dimension, selecting the Month of September 2016 will give all orders for that month.

Can you provide an example app. I cant follow why you are using the aggr function.

Tracy

Re: Use dimension that is not linked to Facts Table

Anonymous — Wed, 25 Jan 2017 10:44:27 GMT

Hi tracy,

see the attached sample.

My cal table and fact table are not linked.

My user is gonna use cal table to select the period he wants to see.

But since the two tables are not linked, he's never gonna see values.

So I want to show in bar of MonthYear = sep-2016 for example, the count of order_code, that has actual_date_from >= 01-09-2016 and actual_date_to <= 30-09-2016.

Re: Use dimension that is not linked to Facts Table

tracysmart — Wed, 25 Jan 2017 11:34:49 GMT

Hi Silvia

does the calendar have to be unlinked? Could you create a field that links them from the dates like the attached?

Would the from and to dates ever span across 2 months?

The expression in the attached chart might work for you but ideally, you would want to link the calendar if possible for performance if nothing else.

Tracy

Re: Use dimension that is not linked to Facts Table

Anonymous — Wed, 25 Jan 2017 11:41:46 GMT

Hi Tracy,

Yes, Calendar table and facts table have to be unlinked.

I'd give you a date example, actual, with from and to values. But in my original app I have 2 more types of date:

- base_line_date_from and base_line_date_to

- forecast_date_from and forecast_date_to

So I do not want to link the two tables.

And yes, the from and to dates can span accros multiple months

Sílvia.

Re: Use dimension that is not linked to Facts Table

rong — Wed, 25 Jan 2017 12:36:05 GMT

Hi Silvia,

If you only need to count the orders for the specific months you don't need to link to your calendar.

You can set-up a calculated dimension

=if(MonthStart(actual_date_from)=MonthStart(actual_date_to),MonthName(actual_date_from))

and add the expression

count(DISTINCT order_code)

see the screenshot below

The only problem is that you have from and to dates that are not in the same month. How do you want to count those, i.e. in what month should those be counted?