Forums for Qlik Data Analytics solutions. Ask questions, join discussions, find solutions, and access documentation and resources.
Forums for Qlik Data Integration solutions. Ask questions, join discussions, find solutions, and access documentation and resources
Share your best Qlik apps and discuss impacts with peers! Show your work and get recognized for innovative uses of Qlik technologies.
Direct links to other resources within the Qlik ecosystem. We suggest you bookmark this page.
Hi there, I'm extremely new to Qlik and am having problems replicating what is a very easy formula (SUMPRODUCT) in Excel in order to get the weighted average of some lines of data. Attached is the table I have created, in order to check the data is correct before I then create graphs. I have tried copying and pasting a few formula's such as AGGR, ONLY and set expression but I must be doing something wrong.
I need a weighted average of the ct_mic column, which should be the total sum of ct_mic*Net Kg divided by total sum of Net Kg.
I also need to know how to exclude the 0 values from this calculation so that the weighted average is correct?
Help!
Hello,
I'm facing the following problem and hope I can explain it well.
There is the following table
Table:
Item | Status | Alternative item
A | x | B
B | x | D
C | | I
D | x | E
E | | H
F | | K
For this I need the following calculation.
If Item is not available (x), there needs to be a check on the alternative item.
I need to know from each item which is the available alternative and the road to it.
Table Solution would look as following
Aggr | Item | Alternative Item
A | A | B
A | B | D
A | D | E
B | B | D
B | D | E
D | D | E
So the check is only done for items that are not available untill first available substitute is found. The items won't be in order, because can replacements can be added any time and removed at any time too (so i dont think peek or previous can be used).
So an applymap() will be used to find the replacement item. This data is available (in my eyes basically the table itself, because all this information is in the table).
My idea was to start with put filter on table to only check if status is 'x' and where there is an alternative item
Then comes to outerloop, (for each loop).
For each vItem in FieldValueList('Item') (this is to check for all items which arent filtered out and to get the field: Aggr
But then comes my problem, how can I add her a do while loop. To keep searching till condition is met (applymap doesnt have a postitive result). My idea was to have vItem to aggr column and also items for the first loop (row) which gives me a replacement item with applymap on the input.
Then this needs to be saved in a variable to get the replacement item for that current item. (then row 1 is done)
And after that the replacement item needs to moved to a new row in the item column to use as input again for the applymap() (row 2) untill there is no replacement is found. After that u go to the next item in Item column and start it all over again till all Items are done
Is this possible and how would I need to write this? My main problems are:
- how to set the variables so they can be used in the loop and that after row 1 the outcome from the mapping will become item of next row and by that also the input of the next mapping iteration untill conditon is met and broken out of innerloop.
- How to nest the do while loop in the for each loop.
Below location has logs only for today. Not sure where to find log file for past few weeks?
C:\ProgramData\Qlik\Sense\Log\Repository\Trace
We basically want to know who changed what setting within QMC --> User Directory Connector.
Hello everyone,
I'm new to this community (and to Qlik as well). I would like if I can run my own SQL queries and how to do that with the couple of data connectors I already created.
Thank you very much,
Emmanuel
Hello everyone,
I came accross a very strange behaviour below:-
I have below dimensions and measures :-
Dimension:- =valuelist('Direct labour N-1', 'Indirect Labour N-1','Total SG&A + R&D N-1')
Measure:-
if(valuelist('Direct labour N-1', 'Indirect Labour N-1','Total SG&A + R&D N-1')='Direct labour N-1',
num(sum({<Indicateur.Code = {'PO.1.161b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
/
sum({<Indicateur.Code = {'PO.1.137b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"}>} [Indicateur.Value])
,'###.#%'),
if(valuelist('Direct labour N-1', 'Indirect Labour N-1','Total SG&A + R&D N-1')='Indirect Labour N-1',
num(sum({<Indicateur.Code = {'PO.1.162b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
/
sum({<Indicateur.Code = {'PO.1.137b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
,'###.#%'),
if(valuelist('Direct labour N-1', 'Indirect Labour N-1','Total SG&A + R&D N-1')='Total SG&A + R&D N-1',
num(sum({<Indicateur.Code = {'PO.1.207b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
/
sum({<Indicateur.Code = {'PO.1.137b'},[Site.isYFPO]={'0'},[Site.Participe]={'49|50'},Date.Year = , Date.MonthName = , Date.Quarter =, Date.Month =, Date.MonthEnd =, MonthYear=,Date=,NewTestvb1=,
Date= {"$(=MonthStart(Max(Date)))"} >} [Indicateur.Value])
,'###.#%')
)
)
)
When i plot the above in bar chart , i get correct number s:-
But when i created a pie chart, it is showing some additional values like below:-
In the above chart, the number corresponding to label 'Values-14.0%' is correct. However, there is an additional label named 'share-17.0%' which i have not calculated but somehow it is appearing out of nowhere.
I am unable to understand why exactly it is working fine in bar chart and causing problem in pie chart.
Can anyone please help me if with this ? 😞
Hello everyone and welcome to the new Ideation Discussion Board!
The Product Insight & Ideas blog has been retired and replaced by the Ideation Discussion board. This will allow the Ideation community to all post and comment on discussions related to the Ideation process. The Ideation rules from the original blog were reposted here and pinned to the top.
A few rules before you post:
So, check it out and start posting! I can't wait to engage with you all.
Meghann
I've looked at several posts looking at counting instances and mine seems unique. It pertains to units messaging (aka "On Air")and I need to identify units that repeatedly message over a period of time including the latest time period (current). Query periodicity is weekly. Each query fetching unit data returns data such as serial number and last message time, plus customer and other info. Most units message multiple times per day but I only fetch the latest each query. I suspect I could count the times a unit shows up over a known period of time and count that number of queries and if the counts match plus it exists in the latest query that would give me the number of current continually messaging units. I also suspect it would work dynamically where if I open the period to say 10 periods and the unit messaged 10 times and existed in the latest period would also exist in all periods under 10. I think it can all be done in set analysis and no extra aggregation in the scripts.
Has anyone done something like that before and can say whether this is the simplest method or would there be another method better suited?
V/r,
John
Hi there
I am struggling to figure out how to count the number of customers that purchased more than 3 times in the past 12 months. The result given by the expression below is empty.
COUNT({<
[Customer ID] = {"=sum({<
[Type]={'P'}, Date={">=$(=date(addmonths(Max(Date),-12),'YYYY-MM-DD'))<=$(=date(Max(Date),'YYYY-MM-DD'))"}
[Total Events])>=3"}
>}distinct [Customer ID])
Please help
March 2021, a year after the world forever changed. It’s so hard to believe it’s been a year already, yet at the same time, it feels like a lifetime. This year we relied more heavily on digital communication than ever before and there was no shortage of communicating in the Qlik Community. One member that stood out to us -- standing by to help, sharing his expertise, and leading by example -- is Qlik Community MVP Lech Miszkiewicz. That is why he is our featured member this month! Lech is in the Community almost every day. He has posted over 4K times, provided countless solutions, and received close to 2K likes.
Lech was born in Poland and lived there most of his life. He earned a Master of Engineering in Commodity Science at the University of Economics and separately a Bachelor of Corporate Management. Lech is married and lives in Sydney Australia. He loves watching Formula 1 and is a big fan of Cirque du Soleil. Lech plays guitar and keyboard. He loves to travel, take photos, and collects wristwatches. If you can believe it, he also runs half marathons and swims! So cool!
Lech began his professional career at Solaris Bus & Coach where he learned QlikView. He later became a QlikView consultant and started working for Hogart Business Intelligence – a Polish Qlik partner. This is where his passion for Qlik started.
In 2015 he was hired by Australian Qlik Elite partner InsideInfo Pty Ltd. and moved to Australia. Lech is still there today and strives to provide good service to InsideInfo’s clients. Lech would like to give a shout-out to his bosses Phil and Stuart!
As a Qlik consultant, Lech works with QlikView, Qlik Sense, NPrinting, and other Qlik products. He spends time in the Qlik Community consuming information from Support, NPrinting, Ideas, and the Tech Preview forums to keep up with the important things. He spends most of his time in the NPrinting forum and leaves the other forums to the likes of Sunny T, Vegar, and many other amazing contributors. Lech recognized that many members experienced the same NPrinting challenges, and so was born his NPrinting blog: https://nprintingadventures.com.
We are so happy and grateful that Lech is part of the Qlik Community. Please join us and recognize him with a “Round of Applause” by leaving a comment and giving a Like to show Lech how much we appreciate him. @Lech_Miszkiewicz @Melissa_Potvin
Hi All,
I have multiple sheets in my application. all the sheets should show past 2 days data upon opening the application. (we have a filter pane object created for this). The field for this list of values are derived from the script written in the backend.
There is two timestamp fields involved (ALL sheets but one uses different timestamp field to get its values).
Now the problem is whenever there is a selection made in filter pane say Filter1, it affects the Filter2 on the other page as well. This should not be the case. I have tried using Alternate state but not succeeded.
Is there any alternative method to achieve this?
Thank you!
MVP
22789MVP
8647Employee
8636MVP
8190MVP & Luminary
7987Employee
6506MVP & Luminary
5335MVP
5172MVP & Luminary
4335Employee
4107Hear from the Qlik Community team about the latest platform enhancements and other topics.
Important and useful support information about end-of-product support, new service releases, and general support topics.
Hear directly from Qlik employees and stay current on the latest and greatest information about Qlik products.
We are bringing together people and resources in response to the pandemic of Coronavirus, aka COVID-19, aka SARS-CoV-2.
Join the QlikView to Qlik Sense group and help you modernize your BI experience through adoption of Qlik Sense.
This group is for healthcare organizations, partners, and Qlik healthcare staff to collaborate and share insights.
Virtual Meetups are now a possibility! With the impact of COVID-19, the Qlik Meetup network has expanded its support to include the ability to host your next Meetup online. Email QlikMeetups@qlik.com to get your virtual Meetup started!’
Qlik can help you manage your supply chain risks during COVID‑19.The nonprofit organization, Direct Relief, uses Qlik’s Supply Chain solution to scale its delivery of medical supplies worldwide.