Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ignore Date dimensions

Hello,

I have the following table of data:

Posting DateStoreQuantity
01.01.2013Big100
01.01.2013Big-30
01.02.2013Big-10
10.02.2013Big-12
15.02.2013Small105
15.02.2013Small-25
20.02.2013Big40
18.03.2013Small-5
27.03.2013Small-2
06.04.2013Small45
01.01.2014Big-50
10.01.2014Big-20
15.02.2014Small-40
24.02.2014Small-10
01.03.2014Big-11
15.04.2014Small-6

What I need is Inventory to date, month or year for every Store. Like this:

StoreCalculates
YearMonthPosting DateBigSmallGrand TotalBigSmall
201388118206=100-30-10-12+40=105-25-5-2+45
170070=100-30=0
01.01.201370070=100-30=0
28880168=100-30-10-12+40=105-25
01.02.201360060=100-30-10=0
10.02.201348048=100-30-10-12=0
15.02.20134880128=100-30-10-12=105-25
20.02.20138880168=100-30-10-12+40=105-25
38873161=100-30-10-12+40=105-25-5-2
18.03.20138875163=100-30-10-12+40=105-25-5
27.03.20138873161=100-30-10-12+40=105-25-5-2
488118206=100-30-10-12+40=105-25-5-2+45
06.04.201388118206=100-30-10-12+40=105-25-5-2+45
201476269

=100-30-10-12+40-50-20-11

=105-25-5-2+45-40-10-6

118118136

=100-30-10-12+40-50-20

=105-25-5-2+45
01.01.201438118156

=100-30-10-12+40-50

=105-25-5-2+45
10.01.201418118136

=100-30-10-12+40-50-20

=105-25-5-2+45
2186886

=100-30-10-12+40-50-20

=105-25-5-2+45-40-10

15.02.2014187896

=100-30-10-12+40-50-20

=105-25-5-2+45-40

24.02.2014186886

=100-30-10-12+40-50-20

=105-25-5-2+45-40-10

376875

=100-30-10-12+40-50-20-11

=105-25-5-2+45-40-10

01.03.201476875

=100-30-10-12+40-50-20-11

=105-25-5-2+45-40-10

476269

=100-30-10-12+40-50-20-11

=105-25-5-2+45-40-10-6

15.04.201476269

=100-30-10-12+40-50-20-11

=105-25-5-2+45-40-10-6

I have Year, Month, Posting Date and Store as Dimensions. This makes useless Set Analysis cause it works for the Selections only (I can deal with it). The problem is to ignore to some extent the Date dimensions and make the Inventory expression calculate the Quantity for all dates before or equal to the Date dimension at the current row.

Any ideas are welcome.

Thanks for Your time!

Best Regards,

Plamen

5 Replies
Contributor III
Contributor III

Hi Plamen,

attached example does not fill your requirement but may be a starting point. Usually you use set analysis to ignore dimensions, however this does only work for dimensions not utilized within a chart itself. You have to use RangeSum functions to calculate across elements of a chart dimension. Search the forum for "running total".

This is such a common requirement but unfortunaltely not quite as trivial as it might seem. Why? Although by use of RangeSum a running total can be calculated, if a user restricts data to 2014 your running total is no more.

The only solution would be to store running total as a fact row by Store and PostingDate. This would allow an expression like

sum(

    {$< PostDate={"$(=Max(PostDate))"} >}

    [QuantityRunningTotal]

)

So the very last value of QuantityRunningTotal for each Store will be summed up. Crucial for this approach would however be a load script that creates the required fact rows. Maybe someone can contribute an example script.

Not applicable

Thank You, Gerhard!

There were some problems in the example Calculates columns - corrected.

But the required results were ok (blue font).

I have tried with RangeSum and Above and it does work correctly when a dimension is entirely collapsed.

But if You expand one row in a dimension, the values for the subtotals of the whole dimension become incorrect - they calculate as if there is no RangeSum but only Sum.

So, the results are ok only for the deepest expanded dimension. As seen in Your attached example, the values in both columns (+/- and Sdo2) are equal on the subtotal rows for Year and Month.

That is because the RowNo() returns values only for the deepest expanded dimension. The subtotals do not return value (or return NULL, I am not quite sure). You may add an expression "=RowNo()" and see the results in different Expand/Collapse variations.

The Set Analysis (as I understand it) includes all the set information in the curly brackets BEFORE the qualifiers (TOTAL, ALL, DISTINCT). So, You can manipulate calculation but You cannot ignore the dimension value of the current row. It can ignore just the SELECTED dimension values. The TOTAL qualifier on the other hand, can ignore the row dimension value. But it ignores it TOTALLY - once ignored, the dimension value cannot be used to limit the period.

I will try to use some kind of Dimensionality filter in the expression with the Above and RowNo approach but I don't know how to escape the Expand/Collapse issue.

Thanks again!

Best Regards,

Plamen

Partner
Partner

Hi!

I've solved exactly problem like this. I solved it in script so that I have warehouse amount for every day. Only downside is that you may get much data...

If you have just small amount of data, do this in script. i can provide sample script.

Br,

Miikka

Climber Finland

Not applicable

Hi, Miikka,

Yes, the script is a solution in general.

But 30000 articles in about 1000 dates and 10 locations on average seems to be far beyond normal data table size.

That's why I am looking for other options.

Best Regards,

Plamen

Contributor III
Contributor III

Hi Miikka,

after some elaboration with Peek() and Previous() functions i managed to calculate a running total column within script. However an expression like sum( {$< PostDate={"$(=Max(PostDate))"} >} [QuantityRunningTotal] ) will NOT work. My current example looks like:

year     | +/- |  QuantityRunningTotal

2010    | 10 |     10

2011    |  -2 |       8

2012    |   5 |     13

Total    | 13 |     31

I still have to use if(Dimensionality()=0,... ) to correct the Total sum(QuantityRunningTotal).


Also my example script does only work disregarding the Store dimension, i did not yet manage to modify it accordingly. If you can share an example of your implementation, please do so as it may enable me to leverage my script. Thank you.


@Plamen

i mildly dislike expressions containing nested if statemens so i try to go down the road of precalculating the total qty per Store and Date. At this point i am not sure about the drawbacks and pitfalls of this approach however...


PS: some useful contributions

How to calculate running total?

Re: Re: How to calculate running total/ average while loading data.

Running Sum on Load Script

Running Total Calculation