topic Re: Cumulative Total in QlikView

Cumulative Total

himabinduch — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

i have below fields

1. Created_date

2.Shipped_date

3.Item

now i want count of Items Which have been shipped and Creted

below is my sample data

ITEM	CREATION_DATE	SHIPPED_DATE
CP90-NP401-20TR	10/09/2015	13/01/2016
CD90-Y8864-6TR	05/03/2015	13/01/2017
CD90-NE884-31TR	06/11/2014	13/02/2015
CP90-NT736-2TR	12/01/2015	13/03/2015
CD90-YA352-1TR	27/01/2016	13/03/2016
CP90-P4969-33TR	18/08/2016	13/03/2017
CD90-Y7602-13HR	19/12/2014	13/04/2015
CD90-Y7632-8TR	22/09/2014	13/04/2015
CD90-Y7636-9	16/01/2015	13/04/2015
CD90-NT069-3TR	05/05/2015	13/06/2015
CP90-P1131-8TR	16/03/2016	13/06/2016
CD90-N6015-20BT	13/07/2014	13/07/2015
CD90-NJ627-51TR	15/12/2014	13/07/2015
CP90-P3860-5TR	18/08/2016	13/07/2017
CD90-ND519-4TR	13/07/2014	13/08/2014
CD90-NE810-49TR	13/07/2014	13/08/2014
CD90-NH190-3TR	13/07/2014	13/08/2014
CD90-Y5714-1	12/07/2013	13/08/2014
CD90-Y5805-1	11/07/2013	13/08/2014
CD90-Y5807-4	11/07/2013	13/08/2014
CD90-Y5832-1TR	14/07/2013	13/08/2014
CD90-Y6236-2	12/07/2013	13/08/2014
CD90-Y7714-3	13/07/2014	13/08/2014
CD90-Y7714-4	13/07/2014	13/08/2014
CP90-N7415-10TR	13/07/2014	13/08/2014
CP90-Y5259-2	29/05/2014	13/08/2014
CP90-Y6193-1TR	12/07/2013	13/08/2014
CD90-Y8121-2TR	02/02/2015	13/08/2017
CD90-ND531-39TR	21/08/2015	13/09/2015
CD90-NK349-7HR	05/08/2014	13/09/2015

Thanks In Advance

Thanks,

hima

Re: Cumulative Total

Anonymous — Wed, 14 Feb 2018 08:58:27 GMT

Hi,

have a look at this!

Then if you want the total you can sum the field COUNT

Re: Cumulative Total

shiveshsingh — Wed, 14 Feb 2018 09:41:34 GMT

you are having 2 columns which shows created date and shipped date.

Now how are you counting which item is shipped? i guess all the item whcih are created are showing shipped in your data.

Please elaborate the expected count.

Re: Cumulative Total

sunny_talwar — Wed, 14 Feb 2018 12:01:56 GMT

Are you hoping to see a count by a date dimension? From what I understand, you might need IntervalMatch‌ where you will interval match between Creation_date and Shipped_date and master calendar.

Re: Cumulative Total

PrashantSangle — Wed, 14 Feb 2018 12:09:00 GMT

what is your expected output??

Re: Cumulative Total

himabinduch — Thu, 15 Feb 2018 10:57:49 GMT

Hi Sunny,

now i have count of shipped items and created items,

in my data i have two created items in 2013Q1 and it got shipped in 2014Q3 and 2015Q4 .

when i am taking this into pivort it is showing for 2014Q3 as 100% and 2015Q4 as 100%

i need output as 2014Q3 50% and 2015Q4 as 100% (cumulative) how i can get it.below is my current data looking like

Thanks,

Hima.

Re: Cumulative Total

sunny_talwar — Thu, 15 Feb 2018 12:00:10 GMT

Without knowing what you have, it would be difficult for me to comment anything... would you be able to share a sample to check this out?

Re: Cumulative Total

himabinduch — Thu, 15 Feb 2018 12:33:42 GMT

Hi Sunny,

i created new blog for the same. and attached the QVW pleas find the blog URL Below

https://community.qlik.com/message/1436109#1436109

Re: Cumulative Total

sunny_talwar — Thu, 15 Feb 2018 12:38:05 GMT

Try this

=RangeSum(Before((count({$<SHIPPED_DATE={'*'}>}DISTINCT ITEM))/(count(TOTAL <YearQuarter>{$<CREATION_DATE={'*'}>}DISTINCT ITEM)), 0, ColumnNo()))