Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So, this is not really a question unless you wish to add to the thread. I am marking it as a question so that I can give points to cool additions.
I was finding that expression writing was not coming as naturally to me as I would like so I spent some time writing out a bunch of simple expressions that control whether a graph is:
I could totally see adding another set of formulas that work for the ENTIRE data set regardless of the date selection.
I am attaching a QVW file but I am also listing the text here for those that only have the free version.
Enjoy.
// ----------------------------------------------------------------------
// | My data set has a DATE, STORE, INVENTORY
// | The date is the date that the inventory level was captured
// | The store is the store location of the inventory
// | The inventory is the number of items counted in inventory
// |
// | The following attempt to look at all of the "simple" expressions
// | that can be used to look at this data in a chart
// |
// ----------------------------------------------------------------------
//
// ----------------------------------------------------------------------
// ABSOLUTE DATA values for EACH DATE within SELECTED DATE RANGE
// ----------------------------------------------------------------------
// SUM([Inventory])
//
// ----------------------------------------------------------------------
// ABSOLUTE DATA values for LAST DATE within SELECTED DATE RANGE
// ----------------------------------------------------------------------
// SUM (if([Date]=max(total [Date]),[Inventory],0))
//
// -------------------------------------------------------------------------
// ABSOLUTE DATA values for FIRST DATE within SELECTED DATE RANGE
// -------------------------------------------------------------------------
// SUM (if([Date]=min(total [Date]),[Inventory],0))
//
// ----------------------------------------------------------------------
// RELATIVE DATA values for EACH DATE within SELECTED DATE RANGE
// RELATIVE TO THE ENTIRE TIME PERIOD
// Not really applicable to this story but since I did it, I thought
// that I would keep it here.
// ----------------------------------------------------------------------
// SUM ([Inventory]) / SUM ( total [Inventory])
//
// ----------------------------------------------------------------------
// RELATIVE DATA values for EACH DATE within SELECTED DATE RANGE
// RELATIVE to EACH SPECIFIC DATE (normalizes ratios)
// ----------------------------------------------------------------------
// SUM ([Inventory]) / SUM ( total <[Date]> [Inventory])
//
// ----------------------------------------------------------------------
// RELATIVE DATA values for LAST DATE within SELECTED DATE RANGE
// ----------------------------------------------------------------------
// SUM (if([Date]=max(total [Date]),[Inventory],0)) / SUM ( total <[Date]> if([Date]=max(total [Date]),[Inventory],0))
//
// -------------------------------------------------------------------------
// RELATIVE DATA values for FIRST DATE within SELECTED DATE RANGE
// -------------------------------------------------------------------------
// SUM (if([Date]=min(total [Date]),[Inventory],0)) / SUM ( total <[Date]> if([Date]=min(total [Date]),[Inventory],0))
//
// ----------------------------------------------------------------------
// ** ** ** ** ** Now some really fun stuff ** ** ** ** **
// ----------------------------------------------------------------------
//
// ----------------------------------------------------------------------
// ABSOLUTE DATA values for FIRST DATE within SELECTED DATE RANGE
// COMPARED TO the LAST DATE within SELECTED DATE RANGE
// ----------------------------------------------------------------------
// SUM (if([Date]=min(total [Date]),[Inventory],if([Date]=max(total [Date]),[Inventory],0)))
//
// ----------------------------------------------------------------------
// RELATIVE DATA values for FIRST DATE within SELECTED DATE RANGE
// COMPARED TO the LAST DATE within SELECTED DATE RANGE
// ----------------------------------------------------------------------
// SUM (if([Date]=min(total [Date]),[Inventory],if([Date]=max(total [Date]),[Inventory],0))) / SUM ( total <[Date]> if([Date]=min(total [Date]),[Inventory],if([Date]=max(total [Date]),[Inventory],0)))
//
Hi,
You can use set analysis for the below expressions, using if in set analysis is not suggestible because of performance issues.
// ABSOLUTE DATA values for LAST DATE within SELECTED DATE RANGE
// ----------------------------------------------------------------------
// SUM (if([Date]=max(total [Date]),[Inventory],0))
You can rewrite this expression as, it purely uses Set Analysis, so the performance is better
=SUM ({<[Date]={'=$(=max(total [Date]))'}>} [Inventory])
//
// -------------------------------------------------------------------------
// ABSOLUTE DATA values for FIRST DATE within SELECTED DATE RANGE
// -------------------------------------------------------------------------
// SUM (if([Date]=min(total [Date]),[Inventory],0))
You can rewrite this expression as, it purely uses Set Analysis, so the performance is better
=SUM ({<[Date]={'=$(=min(total [Date]))'}>} [Inventory])
Regards,
Jagan.
Looks like I have a next path for learning! Thanks Jagan. I will try to get some time next week to re-do the example without all of the if statements.
I found this tool really interesting / fun for playing around with set analysis.
http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=
It's a quick and easy tool you can use to create some complex set analysis with!
Thanks
Tom
Hi,
Hope attached file helps you to optimize qlikview charts.
Regards,
Jagan.