Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Qlik Compose for Data Warehouses
Hello everyone,
in qlik sense
I have a timestamp in a string format and I need to convert it into a date and time with e specific format.
the idea is to be able to create a calendar or extracts month.week...etc from that field, which is not possible when it's a string
Hello ,
I want to create a fiscal year master calendar and I have used the below script :-
//-- Fiscal year Mastr Calendar---//
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(EMLC_OK_DATE) as minDate,
max(EMLC_OK_DATE) as maxDate
Resident ZZ_FF_BUILD_DETAIL_DAILY;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS EMLC_OK_DATE,
week(TempDate) As RFS_Fiscal_Week,
Year(TempDate) As RFS_Fiscal_Year,
Month(TempDate) As RFS_Foscal_Month,
Day(TempDate) As Day,
// YeartoDate(TempDate)*-1 as CurYTDFlag,
//YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as RFS_Fiscal_MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as RFS_Fiscal_Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as RFS_Fiscal_WeekYear,
QuarterName(AddMonths(TempDate,3)) as RFS_Fiscal_QuarterName,
WeekDay(TempDate) as RFS_Fiscal_WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
exit script;
//==============================//
But I am not getting the proper output. Please see below :-
Not sure, what mistake I made in the above script. Can anyone please help me ?
Thanks in advance
Hi,
I'd need to calculate a cumulative sum in the line chart, but this formula doesn't work:
=RangeSum(Above(TOTAL Sum({$<[Type]={'$(vType)'},[Product Line]={'Product 1'},[Account channel]={'Direct'}>} [Value]),1,RowNo(TOTAL)))
I'll have 1 line for actual and 1 line for target in the same line graph and I'd like to cut the actual line - please see the example chart below:
Any idea how to solve this? Thanks!
Hello,
Perhaps somebody can help me with a problem I'm having. I have the following QVD:
Test:
LOAD
CompanyId,
DateId,
StartingDate,
EndDate,
Revenue
The EndDate field value is always the same as the DateId (I use DateId for a different left keep, so I want to keep it that way). StartingDate is a calculated value and depending on DateId always a year plus 1 day back. So if DateId is 1-1-2024, StartingDate value will be 2-1-2023. For each DateId I have a calculated revenue (it is a number field). I want to know the total sum of all the revenue values between the given starting and enddate. So if my value on StartingDate is 2-1-2023, my DateId and EndDate will be 1-1-2024. In the loop it needs to calculate the sum of all the revenue values between the StartingDate and the EndDate. If the starting date is 3-1-2023 and the end date is 2-1-2024, I want the total sum of all the revenue values that fall between those dates. When I have these totals, I can then continue with my script and determine a total value for revenue in the past 12 months for each DateId.
What I would like to do is the following:
LET loopDate = MAKEDATE(2019,1,1);
DO WHILE loopDate <= MAKEDATE(TODAY())
LOAD
CompanyId,
SUM(Revenue) AS Total
RESIDENT Test
WHERE StartingDate >= NUM($(loopDate)) AND EndDate <= NUM(TODAY())
GROUP BY CompanyId;
LET loopDate = loopDate + 1;
LOOP
This loop isn't working however. Can you please help me with this?
If you need any additional information, please let me know.
The screenshot below refers, l want to create a Calculated Field to convert the column EFFDATE into a proper date format. Current format is 04/03/2024 07;06:22 which is a string and not proper date format for trend analysis.
Hi
I trying to write script of an incremental load using 2 fields (VehicleID , RowID).
I would appreciate your help in writing the code for the process of incremental loading.
I want to take each time the new Data (Increment), which does not appear in the existing table, and add it to the existing data (Initial Data) and update if data was changed.
Thanks.
Initial Data:
VehicleID | RowID |
12345 | 1 |
12345 | 2 |
12345 | 3 |
21212 | 1 |
Increment:
VehicleID | RowID |
12345 | 4 |
21212 | 2 |
34343 | 1 |
Hi everyone,
When attempting to retrieve the distinct count of 'app_yhno' across Splty a few values are null.
count(distinct app_yhno) gives counts across all dimensions, whereas applying the aggregation function aggr(count(distinct app_yhno), Splty) only produces values randomly and returns null value.
Specifically, the aim is to obtain a value of 18 for all Anaesthesia in the provided image.
Any help is greatly appreciated.
Hi All,
I have two Vizlib Buttons as mentioned below in Qlik sense. when i click Kye Graphs Button it should be take into key graph sheet with selected value field. But Selected value in field Action is not working . please help me on this issue.
Thanks,
Hi Community,
I have given below data set:
Date | Periods | Franchise | Country | Orders_Numbers |
01.01.2022 | 1 | Burger | USA | 101 |
01.01.2022 | 2 | Burger | Canada | 101 |
01.01.2022 | 3 | Burger | Australia | 101 |
01.01.2022 | 4 | Burger | France | 102 |
01.01.2022 | 5 | Burger | Spain | 102 |
01.01.2022 | 6 | Burger | Italy | 102 |
If I write Expression in the GUI: Count(DISTINCT Orders_Numbers) then I'll get 1 for the Orders_Numbers = 101 whenver I'll select / deselect any dimension on the GUI. If I'll clear all the dimensions then I'll get 2. One for each Orders_Numbers due to Count(DISTINCT Order_Numbers).
I want to achieve the same results using Edit Script. How to do it ? I don't want to remove any dimension Filter from the GUI.
I tried this solutions to achieve the same results but the problem is I got 3 for the Orders_Numbers = 101. Becaue the %Key has a unique Periods in the %Key that makes it unique while I count on the GUI then it returns me the 3 not one due to the %Key. If I write DISTINCT in the Expression then I'll always get 1 for all the data set.
Do I need to change the expression after the below load edit script or what is the right way to do it ?
I use Windows 11, 64-bit 23H2, with 16 GB of RAM. I was using Qlik Sense Desktop November 2033 Patch 2 and everything was working fine. Today, I upgraded my version of Qlik Sense Desktop to the February 2024 Patch, and Qlik opens normally without any error codes or alert messages.
However, when I maximize Qlik to my full HD resolution, the Qlik window fills the screen, but the hub where the applications are located doesn't fill the screen. It appears as a square aligned to the left. I will attach a screenshot for reference.
Have you seen this issue before?
Note: I have hidden the names of the applications and the company logo in the screenshot.
Elevate your career with Connect. 15 Hands-on workshops and certifications for Data and Analytics.
Get to know these Qlik experts driving innovation and excellence in data, analytics and AI.
Browse our helpful how-to's to learn more about navigating Qlik Community and updating your profile.
Your journey awaits! Join us by Logging in and let the adventure begin.
Qlik enables a frictionless migration to AWS cloud by Empresas SB, a group of Chilean health and beauty retail companies employing 10,000 people with 600 points of sale.
Qlik Luminary Stephanie Robinson of JBS USA, the US arm of the global food company employing 70,000 in the US, and over 270,000 people worldwide.
Join one of our Location and Language groups. Find one that suits you today!
A private group is for healthcare organizations, partners, and Qlik healthcare staff to collaborate and share insights..
Qlik Communityの日本語のグループです。 Qlik製品に関する日本語資料のダウンロードや質問を日本語で投稿することができます。
Welcome to the group for Brazil users. .All discussions will be in Portuguese.
Hear from your Community team as they tell you about updates to the Qlik Community Platform and more!