Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
nicouek
Contributor III
Contributor III

Excel and Qlik Sense

Q1 - Can every problem or query solved in Excel be solved using Qlik Sense visualizations or tools.

I am taking an excel course and I tried to create a visualization with Qlik Sense to find the answer to a problem I solved using Excel, and I got confused real fast. The problem was to determine how many orders processed by the entire company in a specific year, were  placed in one quarter but shipped in a different quarter. (Eg: "Order # 00001" placed in Quarter 1 but shipped in Quarter 2 or 3).

Q2 - Can I use my data in Qlik Sense to find the answer?

Q3 - If yes, would I need one or multiple visualizations objects to spit out the answer?

Labels (2)
2 Replies
Dalton_Ruer
Support
Support

Qlik Sense could definitely solve that problem in a number of ways. 

1. Assuming you have a field called QuarterOrdered and a field called QuarterShipped (or dates) you can create a flag while loading. Simply say "IF (QuarterShipped > QuarterOrdered, 1, 0) as ShippedDifferentQuarter"  Then you can simply put a Filter on your screen so the end user can filter to the values that were shipped in another quarter. Any KPI's/Charts you have would immediately be responsive. So if you had expression like Count(OrderID) it would only count the orders that shipped in different quarter. 

2. If you don't want to make the user choose and simply want to show the count of all orders shipped in a different quarter as a KPI on it's own you can simply use an IF condition. Sum(ShippedDifferentQuarter). It simply adds up the 1's that represent the rows that were shipped in a different quarter. 

3. Let's say you want to get the total of the $ values of the orders shipped in a different quarter. You can simply use an expression like "Sum(OrderAmount * ShippedDifferentQuarter)" Gotta love basic math. Anything times 1 is the value, while anything times 0 is 0. 

4. Let's say you want to provide the flexibility for the end user to make a selection, and you don't like 1 and 0 because end users don't know what BOOLEAN values are, and still have the ability to do the cool math stuff. In your Load Script you can use this expression instead which allows you to show Yes or No to end users, but still do the math because Qlik supports a field that has DUAL data types. 

IF (QuarterShipped > QuarterOrdered, Dual('Yes',1) , Dual('No',0) ) as ShippedDifferentQuarter

5. PS - You can also use the IF expression from example 1 inside a Sum function if you don't want to touch the load to just get the value on the fly in a KPI or something. 

Sum(    IF (QuarterShipped > QuarterOrdered, 1, 0) )

 

nicouek
Contributor III
Contributor III
Author

Thank you very much @Dalton_Ruer, I am going to get on it right away. Thank you again.