topic Previuos quarters comparision in QlikView

Previuos quarters comparision

Thu, 07 Oct 2010 06:26:54 GMT

Hi Experts,

I have a excel sheet like this

Yr/ Qtr Product Qty
200901 LAPTOP 2500
200901 LCD TV 4500
200901 WASHING MACHINE 1250
200901 REFRIGERATOR 5400
200902 LAPTOP 5600
200902 LCD TV 1248
200902 WASHING MACHINE 8760
200902 REFRIGERATOR 4322
200903 LAPTOP 8520
200903 LCD TV 5341
200903 WASHING MACHINE 9870
200903 REFRIGERATOR 4320
200904 LAPTOP 1321
200904 LCD TV 6534
200904 WASHING MACHINE 1234
200904 REFRIGERATOR 7654

I am creating one dashboard with the above data, in the dashboard I am going to add one more field with the green and red images.

If I compare between the quarters for any product I should have either red or green image.

ex:
Yr/ Qtr Product Qty Image
200901 LAPTOP 2500 --
200902 LAPTOP 5600 Gree Up Image (because if I compare with the previous quarter of the qty is more. (5600>2500)
200903 LAPTOP 8520 -- Gree Up Image (because if I compare with the previous quarter of the qty is more. (8520>5600)
200904 LAPTOP 1321 -- RED down Image (because if I compare with the previous quarter of the qty is less. (1321<8520)

Finally my dashboard looks like this
Yr/ Qtr Product Qty Image
200901 LAPTOP 2500 --
200901 LCD TV 4500 --
200901 WASHING MACHINE 1250 --
200901 REFRIGERATOR 5400 --
200902 LAPTOP 5600 green
200902 LCD TV 1248 red
200902 WASHING MACHINE 8760 green
200902 REFRIGERATOR 4322 red
200903 LAPTOP 8520 green
200903 LCD TV 5341 green
200903 WASHING MACHINE 9870 green
200903 REFRIGERATOR 4320 red
200904 LAPTOP 1321 red
200904 LCD TV 6534 green
200904 WASHING MACHINE 1234 red
200904 REFRIGERATOR 7654 green

I tried so many ways to compare with the previous quarter, but no luck.

Pls let me know if any one have idea on the same.

Thanks,

Previuos quarters comparision

Fri, 08 Oct 2010 09:56:03 GMT

Hi Experts,

any inputs on these query???

Thanks,

Previuos quarters comparision

johnw — Sat, 09 Oct 2010 00:54:32 GMT

Well, if this is really the lowest level of detail for your data, you could just sort by Product then Yr/Qtr, and add a new field:

if(Product=previous(Product),previous(Qty)) as PreviousQty

That's not what I usually recommend. I usually recommend an AsOf table to tie an AsOfQuarter to the current quarter with a 'Current' type, and the previous quarter with a 'Previous' type. But when your data is this simple, there seems no reason to go to that sort of complexity.

Previuos quarters comparision

Fri, 22 Oct 2010 08:14:08 GMT

Hi John,

Not getting exactly what your suggesting.

Thanks,

Previuos quarters comparision

Tue, 26 Oct 2010 07:33:53 GMT

Hi Experts,

Can you pls guide me.

Thanks,

Previuos quarters comparision

Tue, 26 Oct 2010 07:52:44 GMT

Hi Tims,

You can try the following code. In which, the source of TempSales is your table or excell sheet. The required output would be recieved by Sales Table.

TempSales:
LOAD Left(YrQtr, 4) AS Year,
Right(YrQty, 2) AS Qtr,
Product,
Sum(Qty) AS Qty
FROM TABLE
GROUP BY Left(YrQtr, 4), Right(YrQty, 2), Product
ORDER BY Left(YrQtr, 4), Right(YrQty, 2), Product;

Sales:
NOCONCATENATE
LOAD Year,
Qtr,
Product,
Qty,
if( (Product=previous(Product) AND (previous(Qty) > Qty), 'Green',
if( (Product=previous(Product) AND (previous(Qty) < Qty), 'Red'
)) AS Image
RESIDENT TempSales;

Drop Table TempSales;

Hope, it would be helpful.

Regards,

Raj Kishor

Previuos quarters comparision

Tue, 26 Oct 2010 08:48:10 GMT

Hi Raj,

The if condition is showing error in expression.

Thanks,

Previuos quarters comparision

Tue, 26 Oct 2010 13:01:55 GMT

Hi,

If I used Previous function in the expression tab its not working.

may I know what it makes difference between script and expression tab.

Thanks,

Previuos quarters comparision

johnw — Tue, 26 Oct 2010 20:03:16 GMT


tims_qlikview wrote: If I used Previous function in the expression tab its not working.
may I know what it makes difference between script and expression tab.

Script is script and charts are charts. Some functions can be used in one, some in the other, and some in both. Previous() is not used in charts because it is referring to the previously-read input record from your data source that was not discarded by a where statement. Charts aren't reading data from data sources. Above() can sometimes serve a similar purpose in a chart, but it's not at all the same thing.

In this case, the general categories are "inter record functions" (used in script) and "chart inter record functions" (used in charts).