Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Simple Expressions

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

4 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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
Contributor III
Contributor III

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
Luminary Alumni
Luminary Alumni

Hi,

Hope attached file helps you to optimize qlikview charts.

Regards,

Jagan.