Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Re: how to calculate share using variables in qlik sense

Gross Sales

if(num(GetSelectedCount(Year_new))=0

  or GetFieldSelections(Year_new)='2016' ,

    sum({<[DATE_FORMAT.autoCalendar.Month]=,[DATE_FORMAT.autoCalendar.Date]=

   {"$(= '>=' & '01-04-'& (Date(monthstart(Today()), 'YYYY')) & '<=' & '30-06-'& (Date(monthstart(Today()), 'YYYY')))"}>}GROSS_SALES_VALUE),

     sum({<[DATE_FORMAT.autoCalendar.Month]=,[DATE_FORMAT.autoCalendar.Date]=

    {"$(= '>=' & '01-04-'&Date(AddYears(date('01-04-'& GetFieldSelections(Year_new)),+0), 'YYYY') & '<=' & '30-06-'&Date(AddYears(date('30-06-'& GetFieldSelections(Year_new)),+0), 'YYYY'))"}>}GROSS_SALES_VALUE)

)

Similarly for Gross Return

Highlighted
Partner
Partner

Re: how to calculate share using variables in  qlik sense

Just try to debug each variable.

according to your expression

It should be

(sum(Gross sales) - sum(Gross return))/(sum(Total Gross sales) - sum(Total Gross return))

it will give you share %

Highlighted
Not applicable

Re: how to calculate share using variables in qlik sense

its working this way but i want it dynamic thats why i used variables

but whenever i try to do the sum of a certain variable it fails

eg: in case of above variable

sum($CURR_YTD_SALES)/sum(total($CURR_YTD_SALES))

if i break down the above variable in 2 parts

1.sum($CURR_YTD_SALES)

2.sum(total($CURR_YTD_SALES))

so in both parts i cant sum up the variable

Highlighted
Partner
Partner

Re: how to calculate share using variables in  qlik sense

create different variable CURR_YTD_TOTAL_SALES as

if(num(GetSelectedCount(Year_new))=0

  or GetFieldSelections(Year_new)='2016' ,

    sum(Total {<[DATE_FORMAT.autoCalendar.Month]=,[DATE_FORMAT.autoCalendar.Date]=

   {"$(= '>=' & '01-04-'& (Date(monthstart(Today()), 'YYYY')) & '<=' & '30-06-'& (Date(monthstart(Today()), 'YYYY')))"}>}GROSS_SALES_VALUE),

     sum(Total {<[DATE_FORMAT.autoCalendar.Month]=,[DATE_FORMAT.autoCalendar.Date]=

    {"$(= '>=' & '01-04-'&Date(AddYears(date('01-04-'& GetFieldSelections(Year_new)),+0), 'YYYY') & '<=' & '30-06-'&Date(AddYears(date('30-06-'& GetFieldSelections(Year_new)),+0), 'YYYY'))"}>}GROSS_SALES_VALUE)

)

and then use expression as

$(CURR_YTD_SALES)/$(CURR_YTD_TOTAL_SALES)

View solution in original post

Highlighted
Not applicable

Re: how to calculate share using variables in qlik sense

Its Working But Its Giving 0.00%

Could You Tell me Why?

Highlighted
Partner
Partner

Re: how to calculate share using variables in  qlik sense

Try $(CURR_YTD_SALES) and $(CURR_YTD_TOTAL_SALES) individually and see what values it gives?