topic Re: Help in set analysis in QlikView

Help in set analysis

ali_hijazi — Fri, 24 Oct 2014 07:51:48 GMT

Dears got the following sample data

Branch, Date, Amount

001, 29/3/2014, 500

001, 28/2/2014, 600

001, 31/1/2014, 300

002, 31/5/2014, 750

002, 15,5,2014, 450

003, 30/8/2014, 300

The user on the sheet selects a month and a Year

suppose the selection is September 2014 then I want to get the sum of amount by branch for the max date that is less than 30/9/2014

I have a pivot table with Branch as the dimension

and the result would be like:

001,500

002,750

003,300

whereas if the user selects June 2014 the result has to be

001,500

002,750

Please advise

Re: Help in set analysis

Gysbert_Wassenaar — Fri, 24 Oct 2014 08:05:05 GMT

See attached qvw

Re: Help in set analysis

ali_hijazi — Fri, 24 Oct 2014 08:48:39 GMT

in my case it is returning zero because I might have amounts with zero values

how can I skip zero amounts?

Re: Help in set analysis

Gysbert_Wassenaar — Fri, 24 Oct 2014 08:52:48 GMT

Add a condition to the set modifier to disregard zero values.

Re: Help in set analysis

ali_hijazi — Fri, 24 Oct 2014 08:58:30 GMT

I did but not working

actually I got nulls not zeros in fact

Re: Help in set analysis

Gysbert_Wassenaar — Fri, 24 Oct 2014 09:01:51 GMT

Add a MyField = {'*'} for the field where you want to disregard null values.

Re: Help in set analysis

ali_hijazi — Fri, 24 Oct 2014 09:03:48 GMT

I did but also not working

Re: Help in set analysis

Gysbert_Wassenaar — Fri, 24 Oct 2014 09:05:05 GMT

Post a document that demonstrates the problem.

Re: Help in set analysis

ali_hijazi — Fri, 24 Oct 2014 10:59:42 GMT

your example is not working well

select feb and branch 001 the chart should return 600 not 500

Re: Re: Help in set analysis

Gysbert_Wassenaar — Fri, 24 Oct 2014 11:41:58 GMT

See updated qvw