topic Re: Count in QlikView

Count

Tue, 12 Jan 2016 10:37:23 GMT

Hi All

can any one help me on the following scenario, I have 3 fields Design Date, Sale Date Item , Sale, by using these three fields I would like to calculate Total Months count for each Item from Design date and cumulative sum of Sale for each item to Sale date.

Thanks

John

Re: Count

sunny_talwar — Tue, 12 Jan 2016 10:58:53 GMT

Can you update the excel with your desired output to understand what exactly you are looking for?

Re: Count

Tue, 12 Jan 2016 11:02:14 GMT

Sure

Re: Count

miguelbraga — Tue, 12 Jan 2016 11:17:34 GMT

Here you go. Check the qvw attached:

Good Job

Re: Count

miguelbraga — Tue, 12 Jan 2016 11:18:01 GMT

I assume this is what your looking for

Re: Count

Tue, 12 Jan 2016 11:38:13 GMT

Updated

Re: Count

PradeepReddy — Tue, 12 Jan 2016 11:38:46 GMT

1. total months for each item, Monthdiff(DesignDate,SaleDate)

2. For cumulative sum, please share the sample output, what you are looking for..?

Re: Count

Tue, 12 Jan 2016 11:39:05 GMT

Count is Correct But Sale is not tied , need to work on this , thanks.

Re: Count

miguelbraga — Tue, 12 Jan 2016 11:39:46 GMT

I'll see what I can do

Re: Count

miguelbraga — Tue, 12 Jan 2016 11:47:16 GMT

Maybe Sales is:

= max(Aggr(sum(Sale),Item))

Or this:

= aggr( NODISTINCT sum(Sale), Item )

You choose

Re: Count

PradeepReddy — Tue, 12 Jan 2016 11:56:20 GMT

try bellow expression for cumulative sum.. (Pivot Table)

Dimension: Item,Sale Date

expr1: Sum(Sale)

expr2: if(isnull(above(sum(Sale),1))=1,sum(Sale),rangesum(sum(Sale),above(sum(Sale),1)))

Re: Count

miguelbraga — Tue, 12 Jan 2016 11:58:42 GMT

1. =Sum(MonthsDifference)

2. = aggr( NODISTINCT sum(Sale), Item ) OR max(Aggr(sum(Sale),Item)), depending on how you want to show the results.

Hope it helps

Re: Count

Tue, 12 Jan 2016 12:00:19 GMT

No Luck

Re: Count

Tue, 12 Jan 2016 12:05:24 GMT

Hi Miguel Braga, Could you please explain me how the

MonthDiff variable will function here?

Re: Count

miguelbraga — Tue, 12 Jan 2016 12:10:28 GMT

MonthDiff will function based on how your start date ence [Design Date] is and your end date in your case [Sale Date]. I'm working on the count expression

Re: Count

miguelbraga — Tue, 12 Jan 2016 12:23:12 GMT

See this:

Calculating rolling n-period totals, averages or other aggregations

Or This:

How can I calculate a cumulative sum in load script?

For count

Re: Count

sunny_talwar — Tue, 12 Jan 2016 12:47:15 GMT

Unable to get the results in months, but for days may be this:

Table:

LOAD [Design Date],

[Sale Date],

Item,

Sale

FROM

[Testdata (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate(Table)

LOAD Only(Item) as Item,

Date(Today()) as [Design Date],

Date(Today()) as [Sale Date],

0 as Sale

Resident Table

Group By Item;

Join(Table)

LOAD Min([Sale Date]) as [Min Sale Date],

Item

Resident Table

Group By Item;

FinalTable:

LOAD *,

[Sale Date] - [Min Sale Date] as DaysDifference,

If(Item = Peek('Item'), RangeSum(Peek('Cumulative Mileage'), Sale), Sale) as [Cumulative Mileage]

Resident Table

Order By Item, [Sale Date];

DROP Table Table;

Re: Count

Tue, 12 Jan 2016 14:56:22 GMT

Hi Sunny Thanks, in the Model final table

one more load with Load *, Round(DaysDifference/30.4166666667) As DiffMonth ;

will give us Months.

Thanks again.

Re: Count

sunny_talwar — Tue, 12 Jan 2016 14:57:42 GMT

Awesome