Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys!
I’m new on Qlikview and I’m really stock with a issue, hope you could help me!
My table looks like this:
DATE | WAREHOUSE | GROUP1 | GROUP2 | QTY | CUMULATIVE |
01/14/2014 | W1 | G1-1 | G2 - 1 | 20 | 20 |
01/15/2014 | W1 | G1-1 | G2 - 1 | 3 | 23 |
03/20/2014 | W1 | G1-1 | G2 - 1 | -23 | 0 |
01/05/2014 | W1 | G1-1 | G2-2 | 5 | 5 |
03/10/2014 | W1 | G1-1 | G2-2 | 7 | 7 |
What I need to do is to calculate the stock per month for Group2. To do so, I should get the next result:
DATE | WAREHOUSE | GROUP1 | GROUP2 | TOTAL |
01/31/2014 | W1 | G1-1 | G2 - 1 | 23 |
02/28/2014 | W1 | G1-1 | G2 - 1 | 23 |
03/31/2014 | W1 | G1-1 | G2 - 1 | 0 |
01/31/2014 | W1 | G1-1 | G2-2 | 5 |
02/28/2014 | W1 | G1-1 | G2-2 | 5 |
03/31/2014 | W1 | G1-1 | G2-2 | 12 |
My problems are:
At the end, I will show only the Groups2 that have Total >0 in each month. Any idea how to get it?
Thanks in advance!
Hi Alma, for date dimension you can use:
=Date(Floor(MonthEnd(DATE)))
and the 'TOTAL' expression can be:
If(Above(TOTAL GROUP2)=GROUP2, Above(TOTAL [TOTAL]) + sum(QTY), sum(QTY))
This will work if table is sorted by group2.
And to get dates for all months you need to generate that missing data in QV or wait for another user to give you a better solution.
Generating Missing Data In QlikView
To get rows with TOTAL>0, if TOTAL is the unique expression, you only need to check "Supress zero values" on presentation tab
Gracias Ruben!
Do you know another way to generate just the months I don't have? Because the process will last at least two hours! And the lenght of the project will be much bigger than before
So far I know, you'll need at last one month for each combination of warehouse, group1 and group2, playing with script you can generate only missed data, (120.000*missed months) is a lot of data but I don't know other way.
I keep message in english, easier for other users to give help to generate that data.