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.
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.
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;
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).
