Simple Expressions

[#1]

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:

• Showing absolute values or relative values
• Is showing the first value from a data set, the last value from a data set, or all data in the selected data set.
• For fun, I added a little expression to show both the first data and the last data side by side.

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)))
//

[jagan]

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.

[#3]

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.

[oxtontom]

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

[jagan]

Hi,

Hope attached file helps you to optimize qlikview charts.

Regards,

Jagan.