topic Multiple Dates confusion in QlikView

Multiple Dates confusion

avastani — Mon, 28 Feb 2011 19:57:06 GMT

I have 2 types of dates in my case, Sale Date (Weekly), Inventory Date(weekly), both for a Product and Store combination.

I would like to be able to analyze my data for both Sales and Inventory independently of each other's date selections, i.e. if I pick Sales for 2009, I should be able to pick Inventory for 2010 and so on. I tried with Set analysis to ignore my Calendar selections for Inventory and vice versa for Sales but it doesn't seem to work.

The expressions being

SUM({<InventoryYear=, InventoryWeek=>} QtySold) & SUM({<SalesYear=, SalesWeek=>} QtyInventory)

Am I missing something? I would have expected the engine to treat them both separately no matter what the other non-time dimensions are in the table or chart but it doesn't seem to do so. Any thoughts or suggestions are greatly appreciated. Thanks in advance

Multiple Dates confusion

pover — Tue, 01 Mar 2011 01:24:41 GMT

It looks alright. Are you sure there is no other filter you are selecting that is modifying indirectly the result?

Regards.

Multiple Dates confusion

tabletuner — Tue, 01 Mar 2011 01:42:19 GMT

it depends on how your data model looks like. Do you use concatenated or linktable datamodel to share the product and store dimensions for the fact tables? Are you able to post an example in a miniqvw in which the datamodel is displayed?

Multiple Dates confusion

avastani — Tue, 01 Mar 2011 01:54:43 GMT

no filters selected. seems like it isn't working. i am going to try and create an outer join of the 2 calendar tables with the outer join or all product and store combinations which should work i am thinking.

Multiple Dates confusion

avastani — Tue, 01 Mar 2011 01:56:07 GMT

i use a link table model.

Multiple Dates confusion

avastani — Tue, 01 Mar 2011 02:11:00 GMT

i guess the double outer join solution won't be practical as it creates over a billion records. i probably need a minimal solution

Multiple Dates confusion

avastani — Tue, 01 Mar 2011 16:31:06 GMT

It seems that where this falls apart is when I add the complexity of different Price Levels by Product.

My expression is

SUM({<InventoryYear=, InventoryWeek=>} AGGR(SUM( {<InventoryYear=, InventoryWeek=>}QuantitySold) * SUM( {<PriceLevel = {'$(varSelectedLevel)'}>}Price), Product, SalesYear, ProductLine))

Multiple Dates confusion

pover — Tue, 01 Mar 2011 19:31:38 GMT

Why don't you clear the dates in the Price formula?

SUM({<InventoryYear=, InventoryWeek=>} AGGR(SUM( {<InventoryYear=, InventoryWeek=>}QuantitySold) * SUM( {<InventoryYear=, InventoryWeek=,PriceLevel = {'$(varSelectedLevel)'}>}Price), Product, SalesYear, ProductLine))

SUM({<InventoryYear=, InventoryWeek=>} AGGR(SUM( {<InventoryYear=, InventoryWeek=>}QuantitySold) * SUM( {<SalesYear=, SalesWeek=,PriceLevel = {'$(varSelectedLevel)'}>}Price), Product, SalesYear, ProductLine))

Multiple Dates confusion

avastani — Tue, 01 Mar 2011 19:50:40 GMT

then I get null as my Price. That's where I think the problem is.

It doesn't like {<InventoryYear=, InventoryWeek=>} in the SUM(Price) expression. Just don't understand why... any thoughts?

Multiple Dates confusion

pover — Tue, 01 Mar 2011 20:03:23 GMT

Could you upload a picture of your data model highlighting the relationship of all the fields you use in the formula? What is the varSelectedLevel variable?

Regards.

Multiple Dates confusion

avastani — Tue, 01 Mar 2011 20:13:54 GMT

var SelectedLevel is the Level that the user selects on the front end. Available selections from the Crosslevels table.

Multiple Dates confusion

pover — Tue, 01 Mar 2011 21:50:08 GMT

I think it would be easier to concatenae the SAP and Inventory tables instead of the link table, but regardless can you tell be which of the following formulas work? I have my suspicion that the real problem might be the aggr().

What is the result of the following formulas?

SUM( {<PriceLevel = {'$(varSelectedLevel)'}>}Price)

SUM( {<InventoryYear=, InventoryWeek=,PriceLevel = {'$(varSelectedLevel)'}>}Price)

SUM( AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), Product, SalesYear, ProductLine))

SUM( AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), SalesYear))

SUM( AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), ProductLine))

SUM( AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), Product))

SUM( {<InventoryYear=, InventoryWeek=>} AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), Product, SalesYear, ProductLine))

Or play around with other variations to understand the behavior of the model. After seeing the model, I think this has more to do with the linktable then the handling of 2 dates.

Regards.

Multiple Dates confusion

avastani — Tue, 01 Mar 2011 22:47:34 GMT

how would a concatenated table structure work because the calendar dates are not in common. so if one picks 2010 sales year, it would grey out inventory data unless i found a way to associate it together

Multiple Dates confusion

avastani — Tue, 01 Mar 2011 23:02:26 GMT

Hi Karl,

I can see why you are saying the LinkTable is the culprit. I used

SUM( {<InventoryYear=, InventoryWeek=,PriceLevel = {'$(varSelectedLevel)'}>}Price) and the behavior was that I could select any Inventory Year or week and Prices didn't change. But when I surround that in an AGGR they fall apart. It wouldn't ignore the Inventory calendar selections.

The LinkTable is nothing but a full outer join of the Stores and SKU tables.