Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
amol
Contributor III
Contributor III

Need help with the fiscal year comparison

Hello,

I have a Fiscal Year Data available with me in millions. I want to compare my data in such a manner that the trend in line graph should only show the count from the previous fiscal year. Here my X-axis should be fiscal years and Y- axis should be the count

For example: In the below table: in the fiscal year 2020-2021, 2 orders got repeated from 2019-2020. 

In the fiscal year 2019-2020, 2 orders got repeated from 2018-2019 and so on...

My Dimension is Fiscal Year and I am stucked  with my measure where my line graph should have count of previous fiscal year in this. 

2015-2016 2016-2017 2017-2018 2018-2019 2019-2020 2020-2021
ABCDE MNOPD TRAYL LMNOT TOLON SOLOM
GHIJKL GHIJKL ABCDE ABCDE ABCDE ABCDE
SNOUT LMNOP NOPKE FGHIJ MNOPD LOPQKK
TNOVE TNOVE LMNOP LMNOP LOPQKK LMNOP
SLOTE SUVTW STONE SUVTW SUVTW  
SMNOPE   VTONE KLNOZ    

 

Any help will be really appreciated.

Labels (2)
14 Replies
Ksrinivasan
Specialist
Specialist

hi,

i have taken two field data table, Fiscalyear and Countt,

No need to touch anything, just update your table and reload,

except this script line

If(Fiscalyear='2015-2016','6',AutoNumber(RowNo(),COM_01)) as FINAL;

here you should replace '2015-2016', with very first fiscalyear in your real data and count '6'.

for example: in your real table the first Fiscal year is 2010-2011 and no.of count is 10

then change the script as below:

If(Fiscalyear='2010-2011','10',AutoNumber(RowNo(),COM_01)) as FINAL;

=================================================

// Script

kkk:
LOAD
Fiscalyear,
countt,
AutoNumber(Fiscalyear) as ttt
FROM [lib:/TEST/SSSS1.xlsx]
(ooxml, embedded labels, table is Sheet13);
Store kkk into [lib://TEST/Saaaa.qvd](qvd);
Drop table kkk;
DDD:
LOAD
Fiscalyear,
countt,
(ttt)-1 as ttt
FROM [lib:///TEST/Saaaa.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq);
Store DDD into [lib://TEST/Saaaa1.qvd](qvd);
drop table DDD;
MP:
Mapping LOAD
//Fiscalyear,
// countt,
ttt,
Fiscalyear
FROM [lib://TEST/Saaaa1.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq);
KKKKA:
LOAD
Fiscalyear,
countt,
ttt,
ApplyMap('MP',ttt,'ttt') as TTTT,
countt as CT1
FROM TEST/Saaaa.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
Store KKKKA into [lib://TEST/KKKKA22.qvd](qvd);
Drop Table KKKKA;
Tab1:
Load*,
If(Fiscalyear='2015-2016','6',AutoNumber(RowNo(),COM_01)) as FINAL;
LOAD
TTTT as Fiscalyear,
CT1 as countt,
TTTT&'-'&CT1 as COM_01
FROM [lib://TEST/KKKKA22.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq);
Concatenate
Load*,
If(Fiscalyear='2015-2016','6',AutoNumber(RowNo(),COM_01)) as FINAL;
LOAD
Fiscalyear,
countt,
Fiscalyear&'-'&countt as COM_01
FROM [lib://TEST/Saaaa.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq)
where not Match(Fiscalyear,'ttt');
Store Tab1 into [lib://TEST/Tab1.qvd](qvd);
Drop Table Tab1;
Tab2:
LOAD
Fiscalyear,
countt,
COM_01,
FINAL
FROM [lib://EST/tab1.csv]
(txt, utf8, embedded labels, delimiter is ',', msq)
where (FINAL>'1');

 

Result will be:

Ksrinivasan_0-1613410594476.png

most of the Qlik community wiseman's requirements always like iceberg.

ksrinivasan.

 

joseph_morales
Creator II
Creator II

Hi @amol 

You could also try this way, I'm loading the data inline, you just have to replace it with your data.

//load the data, and create an id for the Fiscal Year field.
TEMP_DATA:
LOAD *,
AutoNumber([Fiscal Year])		as [ID_Fiscal Year];
LOAD * INLINE [
Fiscal Year, Orders
2015-2016,ABCDE
2015-2016,GHIJKL
2015-2016,SNOUT
2015-2016,TNOVE
2015-2016,SLOTE
2015-2016,SMNOPE
2016-2017,MNOPD
2016-2017,GHIJKL
2016-2017,LMNOP
2016-2017,TNOVE
2016-2017,SUVTW
2017-2018,TRAYL
2017-2018,ABCDE
2017-2018,NOPKE
2017-2018,LMNOP
2017-2018,STONE
2017-2018,VTONE
2018-2019,LMNOT
2018-2019,ABCDE
2018-2019,FGHIJ
2018-2019,LMNOP
2018-2019,SUVTW
2018-2019,KLNOZ
2019-2020,TOLON
2019-2020,ABCDE
2019-2020,MNOPD
2019-2020,LOPQKK
2019-2020,SUVTW
2020-2021,SOLOM
2020-2021,ABCDE
2020-2021,LOPQKK
2020-2021,LMNOP
];

//concatenate all orders with the separator "|" and add +1 to the field ID_Fiscal Year, 
//to assign the contamination of the orders to the next period.
left join (TEMP_DATA)
Load
[ID_Fiscal Year]+1 		as [ID_Fiscal Year],
Concat(Orders,'|')		as Concat_Orders
resident TEMP_DATA
group by [ID_Fiscal Year]
order by [Fiscal Year] asc
;


//With substringcount I will be able to determine which of the orders is 
//being repeated with the previous fiscal year.
DATA:
Load *,
SubStringCount(Concat_Orders,Orders)   as [Exists Repeated]
Resident TEMP_DATA;

Drop table TEMP_DATA;
Drop field Concat_Orders,[ID_Fiscal Year] from DATA;

joseph_morales_0-1613420280717.png

 

Best Regards,
Joseph Morales
amol
Contributor III
Contributor III
Author

Hi @joseph_morales ,

The data is not limited. It is in Millions. The above code won't work.

 

Amol

Ksrinivasan
Specialist
Specialist

hi,

find the script

Tab1:
LOAD
Fiscalyear,
(Left(Fiscalyear,4)+1) &'-'& (Left(Fiscalyear,4)+2) as Fiscalyear_Du,
countt
FROM [lib://TEST/SSSS1.qvd]
(ooxml, embedded labels, table is Sheet13);
Concatenate
Load
Fiscalyear_Du as Fiscalyear,
countt
Resident Tab1;
Store Tab1 into [lib://TEST/Tab2.qvd](qvd);
Drop Tables Tab1;
Tab2:
LOAD
Fiscalyear,
// Fiscalyear_Du,
countt,
if(Fiscalyear='2015-2016','2',AutoNumber(RowNo(),Fiscalyear&'-'&countt)) as CC_Nt
FROM [lib:///TEST/tab2.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq);

chart:

Dim: =Fiscalyear

Measure: =count( {$<CC_Nt={"=(CC_Nt)>1"}>} CC_Nt )

result:

Ksrinivasan_0-1613664048317.png

 

ksrinivasan

 

amol
Contributor III
Contributor III
Author

Thank you so much @Ksrinivasan . I will try to implement the above logics and get back to you.

 

Amol