# 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
Did you mean:
Not applicable

## Ignore Date dimensions

Hello,

I have the following table of data:

 Posting Date Store Quantity 01.01.2013 Big 100 01.01.2013 Big -30 01.02.2013 Big -10 10.02.2013 Big -12 15.02.2013 Small 105 15.02.2013 Small -25 20.02.2013 Big 40 18.03.2013 Small -5 27.03.2013 Small -2 06.04.2013 Small 45 01.01.2014 Big -50 10.01.2014 Big -20 15.02.2014 Small -40 24.02.2014 Small -10 01.03.2014 Big -11 15.04.2014 Small -6

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

 Store Calculates Year Month Posting Date Big Small Grand Total Big Small 2013 88 118 206 =100-30-10-12+40 =105-25-5-2+45 1 70 0 70 =100-30 =0 01.01.2013 70 0 70 =100-30 =0 2 88 80 168 =100-30-10-12+40 =105-25 01.02.2013 60 0 60 =100-30-10 =0 10.02.2013 48 0 48 =100-30-10-12 =0 15.02.2013 48 80 128 =100-30-10-12 =105-25 20.02.2013 88 80 168 =100-30-10-12+40 =105-25 3 88 73 161 =100-30-10-12+40 =105-25-5-2 18.03.2013 88 75 163 =100-30-10-12+40 =105-25-5 27.03.2013 88 73 161 =100-30-10-12+40 =105-25-5-2 4 88 118 206 =100-30-10-12+40 =105-25-5-2+45 06.04.2013 88 118 206 =100-30-10-12+40 =105-25-5-2+45 2014 7 62 69 =100-30-10-12+40-50-20-11 =105-25-5-2+45-40-10-6 1 18 118 136 =100-30-10-12+40-50-20 =105-25-5-2+45 01.01.2014 38 118 156 =100-30-10-12+40-50 =105-25-5-2+45 10.01.2014 18 118 136 =100-30-10-12+40-50-20 =105-25-5-2+45 2 18 68 86 =100-30-10-12+40-50-20 =105-25-5-2+45-40-10 15.02.2014 18 78 96 =100-30-10-12+40-50-20 =105-25-5-2+45-40 24.02.2014 18 68 86 =100-30-10-12+40-50-20 =105-25-5-2+45-40-10 3 7 68 75 =100-30-10-12+40-50-20-11 =105-25-5-2+45-40-10 01.03.2014 7 68 75 =100-30-10-12+40-50-20-11 =105-25-5-2+45-40-10 4 7 62 69 =100-30-10-12+40-50-20-11 =105-25-5-2+45-40-10-6 15.04.2014 7 62 69 =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.

Best Regards,

Plamen

5 Replies
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

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

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?